Calculating Percentage on Hierarchies in DAX

DAX does not allows us to calculate hierarchies directly. In fact there’s no DAX function that handles hierarchies. But we can calculate them using some tricks. In this article, I am going to explain how we can calculate percentages on a geographic data.

We have some of the following natural hierarchies in the database world.

  • Product-Subcategory-Category,
  • City-State-Country-Continent,
  • Year-Quarter-Month-Day,
  • Hour-Minute-Second.

Fig-1 We have only three tables Sales, Stores, Products in this data model

The aim of this article is to:

  1. Compare a specific City’s performace with it’s Country,
  2. Compare a specific Country’s performace with it’s Continent,
  3. Comapre a specific Continent’s performace with the whole world.

Firstly you need to create some hierarchies on the stores table like the following. When creating hierarchies, add items from wider granularity to narrow i.e. Continent-Country-City etc

Creating Hierarchies:

  1. Go to diagram view in power pivot and right-click on the stores table,
  2. Click on create hierarchy,
  3. Create a name for the hierarchy i.e. Geography in this case,
  4. Right click on Continent and add to hierarchy,
  5. Do it for Country and City.

After creating hierarchies, it should look like this on your stores table in the diagram view.

Fig-2 Hierarchies from the Stores table

We need some DAX measures here

  1. TotalSales to compare the sales with other granularities,
  2. CountryLevel to compare a City’s performance with all the cities in that Country,
  3. ContinentLevel compare a Country’s performance with all the countries in that Continent,
  4. WorldwideLevel to compare a Continents’ performance gainst all the Continents in the world.
  • TotalSales :=

SUMX ( Sales, Sales[Qty] * RELATED ( Products[Price] ) )

  • CountryLevel :=

CALCULATE ( [TotalSales], ALL ( Stores[City] ) )

  • ContinentLevel :=

CALCULATE ( [TotalSales], ALL ( Stores[Country] ) )

  • WorldwideSales :=

CALCULATE ( [TotalSales], ALL ( Stores[Continent] ) )

Your output will be like this:

Fig-3 This shows only sales with dollar values

At this Level we can see TotalSales is showing sales for that City and both CountryLevel and ContinentLevel are meaningless.

This is the reason we need another set of DAX measures to see the same sales figures in percentages on their respective granularities i.e. City on Country, Country on Continent etc

  • CountryPct to show the percentage over the Country,
  • ContinentPct to show the percentage over the Continent,
  • AllWorldPct show the percentage over the AllTheWorld.

 

CountryPct:=[TotalSales]/[CountryLevel]

ContinentPct:=[TotalSales]/[ContinentLevel]

AllTheWorldPct:=[TotalSales]/[WorldwideSales]

 

We can skip the measures that are with sales values from the pivot table and show only their percentages for better understanding and comparison against their granularities.

It looks like this now!

Fig-4 This shows the sales percentages

At this point, we can understand clearly that:

If the pivot table report is at City Level then CountryPct measure shows the correct values leaving other two values ContinentPct and AllTheWorldPct meaningless.

This behaves same way for other levels as well. That means it shows correct figures only for that level which is on the pivot table rows i.e. City in the above image.

To reactify the problem, we need another awesome measure which will show the correct values at all hierarchy levels.

PercOnParents :=
IF (
    ISFILTERED ( Stores[City] ),
[CountryPct],
    IF (
        ISFILTERED ( Stores[Country] ),
[ContinentPct],
        IF ( ISFILTERED ( Stores[Continent] ), [AllTheWorldPct] )
    )
)

Very easy formula aah? But you can expand the formula and frighten the readers like the below

Things are much better now!

When pivot table rows are at City level

Fig-5- Showing the Cities’ performance at their Country level

 

When pivot table rows are at Country level

Fig-6 Showing the correct values for Continent level performance of those Countries on rows.

When pivot table rows are at Continent level

Fig-7 Showing the World level performance of those Continents on the rows.

By the way, you can use a slicer for items otherthan Stores table i.e. you can slice the same by a product, customer, year etc.

If you use any column from Stores table this will show you meaningless sales values.

See for example here

Fig-8 Shows the correctly honoring all the selections on the slicer

But if you select something from the Stores table, it will behave in a weird manner

Fig-9 Percentages are not total up to 100% here

Do you see those percentages are not totalling up to 100%? These slicers are the culprit here.

As I select something from the slicers exactly, only those items are showing up in the rows.

Nothing new here. This is the slicers’ default behavior. But we need those slicers to expand /collapse the hierarchies so we can’t really ditch the slicers. But we are ending up with wrong percentages.

What I want to see here is:  The break up of the performance only for the two Cities which are on the slicers like for eg Chennai- 49.52% and Bangalore-50.48%

We are going to build a new hierarchy here like the below one.

Some Keys points here:

  • First we built these calculated columns with prefix H in the Stores table,
  • We built a new hierarchy with the same,
  • We hide the columns from the client tools,

Now build the previous formula with these new columns which looks like:

HPercOnParentsFreightening :=
IF (
    ISFILTERED ( Stores[HCity] ),
    SUMX ( Sales, Sales[Qty] * RELATED ( Products[Price] ) )
CALCULATE (
            SUMX ( Sales, Sales[Qty] * RELATED ( Products[Price] ) ),
            ALL ( Stores[HCity] )
        ),
    IF (
        ISFILTERED ( Stores[HCountry] ),
        SUMX ( Sales, Sales[Qty] * RELATED ( Products[Price] ) )
CALCULATE (
                SUMX ( Sales, Sales[Qty] * RELATED ( Products[Price] ) ),
                ALL ( Stores[HCountry] )
            ),
        IF (
            ISFILTERED ( Stores[HContinent] ),
            SUMX ( Sales, Sales[Qty] * RELATED ( Products[Price] ) )
CALCULATE (
                    SUMX ( Sales, Sales[Qty] * RELATED ( Products[Price] ) ),
                    ALL ( Stores[HContinent] )
                )
        )
    )
)

Now see the magic!

The beauty of this formula is, it works even if you select two cities from different countries on the slicers.

Leave a Reply

Your email address will not be published. Required fields are marked *
You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>