PowerPivot DAX – Percentage of Total

Need a percentage of total  measure in DAX?   Read on…

Lets say you want a report on products, showing % of each product’s Sale Amount to the total Sale Amount for all products selected.

Business Formula is SUM(Product Sales) / SUM (All Selected Product Sales).

Best to split the formula up into a set of seperate measures, then create the final % measure off these building measures.  But first, you ideally should have at least two tables in your PowerPivot model, one is Sales (lets call this table “FactSales”) with a measure called “SalesAmount” and the other (lets call this table “DimProducts”) with a join on Product to Product (called “DimProduct”).

Lets go forth and create 3 measures in the calculations area of your FactSales table :

SelectedProductTotal:=SUM(FactSales[SalesAmount])

AllSelectedProductsTotal:CALCULATE(SUM([SalesAmount]),ALL(DimProduct[Product]))

% Product Sales of Total:SelectedProductTotal/AllSelectedProductsTotal

Now in your report, put the Product attribute from DimProduct on the rows, and the measure % Product Sales of Total in the values.

About AussieBICG

Connect with me here https://au.linkedin.com/in/aussiebicg

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s