Percent of total in a hierarchy (DAX)

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

Geography 1

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

Hierarchy Excel 2

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.

Hierarchy Excel 3

I did not show all the cities in the screenshot above

Advertisements

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