Hierarchy Drill Down Logic

[Anonymous]

Hello,

I have a dataset that I would like to summarize in a matrix with a drill down option. I have attached a general straw man of what my data will look like. For this example, there are 4 levels in the hierarchy and level 4 is the most granular level while 1 is the most general. The problem that I am having is that for the drill down, all of the totals going up by level are not raw sums, and as a result I have a "total" entry for each level (Ex: a total for level 3a that is composed of 4a and 4b). Furthermore, I was wondering if there was a way for me to tell the data to take the total for the outer levels instead of aggregating by summing?

Note, all totals are greater than the individual components so I did try to make the data column aggregate by max, however, I ran into the issue of each of the total rows showing up in the drill down with the individual component (Ex: for the 3a drill down I had a line for 4a, 4b, and total). Therefore, I would like to figure out a measure that has the logic described above.

Please let me know if this is not clear or if more information/context is needed.

Thanks,

Nick

Level 1	Level 2	Level 3	Level 4	Data
Category 1	Total	Total	Total	x
Category 1	Category 2a	Total	Total	x
Category 1	Category 2b	Total	Total	x
Category 1	Category 2a	Category3a	Total	x
Category 1	Category 2a	Category3b	Total	x
Category 1	Category 2b	Category3c	Total	x
Category 1	Category 2b	Category3d	Total	x
Category 1	Category 2a	Category3a	Category 4a	x
Category 1	Category 2a	Category3a	Category 4b	x
Category 1	Category 2a	Category3b	Category 4c	x
Category 1	Category 2a	Category3b	Category 4d	x
Category 1	Category 2b	Category3c	Category 4e	x
Category 1	Category 2b	Category3c	Category 4f	x
Category 1	Category 2b	Category3d	Category 4g	x
Category 1	Category 2b	Category3d	Category 4h	x

[v-robertq-msft · Accepted solution]

Hi, @Anonymous 

You can try this measure:

Measure = IF(

    ISINSCOPE('Table'[level4]),

    SUM('Table'[Data]),

    IF(ISINSCOPE('Table'[level3]),

    CALCULATE(

        VALUES('Table'[Data]),

        FILTER(ALL('Table'),'Table'[Category] = MAX('Table'[level3]))),

        IF(ISINSCOPE('Table'[level2]),

        CALCULATE(

            VALUES('Table'[Data]),

            FILTER(ALL('Table'),'Table'[Category] = MAX('Table'[level2]))),

            CALCULATE(

                VALUES('Table'[Data]),

                FILTER(ALL('Table'),'Table'[Category] = MAX('Table'[level1]))))))

And you can get what you want, like this:

You can refer to the pbix file here:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/xiaoxin_qiuyunus_onmicrosoft_com/EbBdKfC-VYRJkSCBrmZsjWABXKdWMwQ5v7AHosGvqjfrPw?e=vJweci 

If this solution can not meet your requirement, please show me your expected output and detailed logic.

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

This helped thank you. However, I am now having a problem in my measure with looking up the quarter. I have copied below the formula below. My issue is that although I have stated I would like to look at the previous quarter, my formula is still looking up the current quarter. If there are any context pieces that I am missing please let me know.

Thanks,

Nick

Prior Quarter =

VAR PriorQuarter = PREVIOUSQUARTER('Output for BI Testing'[Date])

RETURN

IF(

ISINSCOPE('Output for BI Testing'[Level 5]),

CALCULATE(SUMX('Output for BI Testing', [Report_Amount]),

FILTER('Output for BI Testing', AND([Category] = 5, AND([Level 5] <> "Total", PriorQuarter ) ) ) ),

IF(ISINSCOPE('Output for BI Testing'[Level 4]),

CALCULATE(SUMX('Output for BI Testing', [Report_Amount]),

FILTER('Output for BI Testing', AND([Category] = 4, AND([Level 4] <> "Total", PriorQuarter ) ) ) ),

IF(ISINSCOPE('Output for BI Testing'[Level 3]),

CALCULATE(SUMX('Output for BI Testing', [Report_Amount]),

FILTER('Output for BI Testing', AND([Category] = 3, AND([Level 3] <> "Total", PriorQuarter ) ) ) ),

IF(ISINSCOPE('Output for BI Testing'[Level 2]),

CALCULATE(SUMX('Output for BI Testing', [Report_Amount]),

FILTER('Output for BI Testing', AND([Category] = 2, AND([Level 2] <> "Total", PriorQuarter ) ) ) ),

CALCULATE(SUMX('Output for BI Testing', [Report_Amount]),

FILTER('Output for BI Testing', AND([Category] = 1, PriorQuarter ) ) )

))))

[Anonymous]

Also, I have done some spot checks on quarters that do not have data for previous quarters and the formula knows that and shows 0 for those quarters. The issue arises when the data is there for prior quarters.