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.