Get percent of total in DAX using ALLSELECTED()

To get percent of total in DAX we can use the ALLSELECTED function. To demonstrate how this can be achieved I will make 3 calculated measures in my model.

1. TotalSales:=CALCULATE(SUM([Order Quantity]);ALLSELECTED())
2. Sum Order Quantity:=Sum([Order Quantity])
3. Percent Of Total:=[Sum Order Quantity] / [TotalSales]

The first measure will give the total quantity. The second one will give sum quantity for each dimension member. The last one will give the percent. To make the last one nice and readable you can format it as percent.

This is how it will look in an Excel pivot

Percent of total

This easy approach will only work on the first level in a hierarchy. To make it work on multiple levels you have to extend the code. But that will be an other post 🙂

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