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 :



% 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.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s