Getting percent of total in a hierarchy is easy in DAX. In this example we will use the Geography hierarchy.

Then I will create 2 calculated measures in my model.

TotalSales2:=calculate(SUM([Order Quantity]);ALL('Geography'))

And

Ratio To Total:=Sum([Order Quantity]) / [TotalSales2]

The last one should be formatted as percent.

Then process and deploy your model.

If you open an Excel pivot you should see something like this

If you summarize “Ratio To Total” in this pivot you will get 100 %.

When changing the pivot to show city instead of country the percent still works as intended.

I did not show all the cities in the screenshot above

Advertisements