Slicer Hierarchy Help

[rel2022]

Hi,

I'm new to PowerBI and trying to figure out how to solve a problem related to adding multiple filters to a slicer and the hierarchy it creates.

My data is made up of survey questions, the average score per question, and the group the data belongs to (there are multiple columns for all of the sub-groups. Some groups have many levels, some don't). So it looks something like this made up data below (but there are a lot more questions and a lot more groups/sub-groups):

Question	Score	Level 1	Level 2	Level 3	Level 4
1	55	Group A	Subgroup A1	Subgroup A1.1	Subgroup A1.2
2.	66	Group A	Subgroup A1	Subgroup A1.1	Subgroup A1.2
3.	44	Group A	Subgroup A1	Subgroup A1.1	Subgroup A1.2
1.	77	Group A	Subgroup B1	Subgroup B1.1	Subgroup B1.2
2.	54	Group A	Subgroup B1	Subgroup B1.1	Subgroup B1.2
3.	32	Group A	Subgroup B1	Subgroup B1.1	Subgroup B1.2
1.	82	Group A	Group A	Group A	Group A
2.	58	Group A	Group A	Group A	Group A
3.	39	Group A	Group A	Group A	Group A

Power BI correctly makes the hierarchy in the slicer:

But the problem I'm running into is that it allows users to select the top-most parent leaf (Group A in this case) and that selects all of the leaves underneath and it averages all the values from Subgroup A1, Subgroup B1, Group A, etc. to calculate the average score for all of the questions in Group A. Unfortunately, this leads to incorrect calculations in my visuals b/c there aren't individual entries for every small sub-group if the sample size was too small, so calculating averages like that doesn't work. Instead, we need people to select the child "Group A" if they want to see the overall scores for all of Group A. Similarly, if they want to see the scores for a sub-group, they should click the appropriate child leaf (e.g., Subgroup B1.2).

Can anyone think of a way around this problem? I don't think there's a way to only allow them to select the child leaf, so I need another way around this issue.

Thanks in advance!

[v-jingzhang]

Hi @rel2022 

Currently I don't understand what is the correct average score you want to achieve for every group you select. Can you please introduce how you want to calculate the average for Group A and how to calculate the average for a sub group based on some sample data?

And what is the visual that's used to display the average measure? What is your average measure's DAX code? We may need to modify the measure according to the expected visual you want to output. 

Best Regards,
Community Support Team _ Jing

[rel2022]

Thanks for your response, @v-jingzhang 

The average is just calculated based on creating a new measure and using the Calculate function and filtering based on the questions that should be part of that "index". For example: 

Index 1 =

CALCULATE(AVERAGE('Sheet1'[Score]), 'Sheet1'[Question] IN { 1, 2})

I'm using 3 types of visuals: bar charts, line charts, and tables.

The problem arises when I make the slicer with the multiple filters/hierarchy. If someone uses the slicer and clicks "Group A", they will get average scores that is made up of the data from Group A, Subgroup A1.2, and Subgroup B1.2 since Group A is the parent for all of those and the slicer automatically selects everything that falls under Group A.

The desired outcome is they would only get the averages that make up Group A's data - which is the last 3 lines of the sample data above. That is just one example, it happens all over the place anytime a parent node has multiple child nodes. This happens because my dataset isn't the full raw dataset, the scores are already averages and not the entire raw dataset (which isn't available), and doesn't have every sub-group if their sample size was too small (their data is just captured in the average of the parent group).

So basically, I need a way to only have the visuals update if the person clicks the lowest sub-group level and it should just display the results from those lines in the dataset. Which sometimes is Level 4 in my dataset, but other times could be Level 1, 2 or 3.

Thanks for your help!

[amitchandak]

@rel2022 , You should use isinscope in your calculations

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

[rel2022]

@amitchandak Thanks for the response. I'm new to all this, would you mind elaborating on how I'd use isinscope to help with this problem? My visuals mostly use measures to calculcate the average of either all of the questions or specific sub-sets of them. Thanks!