Percentages over Hierarchies

[filipebodas]

Hello guys,

I would like some help regarding how do you calculate % over hierarchy levels.

I have a matrix, which has a the final results but it is missing a litte twist regarding how my months are ordered.

This is my solution, here you can see the 2.162 has 98% of 2019's Total and the following percentages as 17,7% of the 98% for august, and so on. 

However, when I order my month in my calendar table by month number, the same matrix stays like this:

Can someone explain to me why this happens? And if possible, how can I turn this around.

As for the DAX measure that calculates the %, I followed this post by Arpita that is really nice: https://medium.com/geekculture/calculating-percentages-over-hierarchies-in-dax-power-bi-chapter-8-c3...

% Orders All Levels =

VAR CurrentOrders = [Total Orders]

VAR PenuriasOrders =

     CALCULATE (

     [Total Orders],

     ALLSELECTED('Calendar'[Month Name])

     )

VAR YearOrders =

     CALCULATE (

     [Total Orders],

     ALLSELECTED(Orders[Penúria])

     )

VAR TotalOrders =

     CALCULATE (

     [Total Orders],

     ALLSELECTED('Calendar'[Year])

     )

VAR RatioToParent =

     IF (

     ISINSCOPE ( 'Calendar'[Month Name] ),

     DIVIDE ( CurrentOrders, PenuriasOrders, 0),

     IF (

         ISINSCOPE ( Orders[Penúria]),

         DIVIDE (CurrentOrders, YearOrders, 0),

            IF (

            ISINSCOPE( Calendar[Year] ),

            DIVIDE (CurrentOrders, TotalOrders, 0)

            )))

Return RatioToParent

[v-jayw-msft]

Hi @filipebodas ,

Try using ALLEXCPET() function instead of ALLSELECTED() function.

https://docs.microsoft.com/en-us/dax/allexcept-function-dax 

If it doesn't help, you can check which variable is wrong by returning each variable separately.

Best Regards,

Jay

[filipebodas]

It didn't solve. The strangest thing is that with the Month Number it calculates correctly but if I change the variable from Month Number to Month Name, it gives a different value for the % of the month. But either Month Number and Month Name come from the Calendar Table.

[v-jayw-msft]

Hi @filipebodas ,

Can you share some sample data? It's quite hard to figure out what's wrong in the formula without any data to test.

Best Regards,

Jay

[VahidDM]

Hi @filipebodas 

Can you post sample data as text and expected output?
Not enough information to go on;

please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/