Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Not applicable

How to change calculations based on level of hierarchy

Hi, I've run into an interesting situation with some survey data that I'm unsure how to answer. The survey is a list of questions where users are asked to rate something between a value of 1 thru 10.


To get the "Score" of a given question, it's a simple average - sum(results) / count(results).


But the questions are also grouped into "question categories" - and to get the "Score" for a question category, we can't just continue the sum(results) / count(results) for all  of the question in it. Instead we have to AVERAGE the individual question results. What this means is that a question with only 1 response counts as much as a question with 10,000 responses when calculating the question category. Don't ask why - its a government requirement.


I'm trying to write a measure that works at both the question and question category levels, but I really have no idea how to do this. I'm uncertain how to make the measure "know" which level is being selected on a visualization, for instance...and even if I did know that, how to pick one formula vs. another.


Any ideas? I'm not sure where to even get started on this.


Thanks in advance!


Advocate II
Advocate II

Hi there,

I highly recommend this video to learn how to calculated differently based on hierarchy levels 🙂

Not applicable

One more thing that might make this more clear. We have two types of questions and question categories: "TopBox" and "Means".


The calculations are:


At the QUESTION level:

Question level Mean type = sum(scores) / count(scores)

Question level Topbox type = sum(scores) / count(scores)




Question Category Mean type = sum(scores) / count(scores) for all questions aggregated up...i.e. still a simple average

Question Category Topbox type = average of the topbox scores for the individual questions below the category



So when I'm trying to define the calculations, it depends on what level of the hierarchy I'm at (question vs. question category), and whether the question or question category is Topbox or Means style of calculation.


Sample Data:


Means style:

Question 1 :   87%

Question 2 :   94%

Question category total: 93% (because question 2 had significantly higher responses than question one, it weights the category calculation)


Topbox style

Question 1: 50%

Question 2: 100%

Question Category total: 75% (because topbox averages the individual question totals, it doesn't matter if question 2 has 1000 answers and question 1 only has 2 answers...the result is a average of the two questions)


I hope this helps!




Community Support
Community Support



A measure operates on aggregations of data defined by the current context and you may use CALCULATE Function to change the context.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Not applicable

Hi Sam,


unsure how this tells me whether I'm at the lowest level "question" of the hierarchy or at the "question group" level, and how it would pick a different calculation formula based on that. Is there something I'm missing?


I'm thinking I need to use "ISFILTERED" to try to figure out which level I'm at...still working on it.




Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors