Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
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!

Scott

4 REPLIES 4
theov
Advocate II
Advocate II

Hi there,

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

 

https://www.youtube.com/watch?v=LV9LqcEmgFc

Anonymous
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)

 

 

At the QUESTION CATEGORY level:

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!

Scott

 

 

v-chuncz-msft
Community Support
Community Support

@Anonymous,

 

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

https://community.powerbi.com/t5/Desktop/Subtotal-Percentages/m-p/313658#M139139

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.
Anonymous
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.

 

Thanks!

Scott

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors