Forum Discussion

rel2022's avatar
rel2022
Frequent Visitor
4 years ago

Slicer Hierarchy Help

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 av...
Need Help
v-jingzhang's avatar
v-jingzhang
Community Support
4 years ago

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's avatar
rel2022
Frequent Visitor
4 years ago

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!