How to change calculations based on level of hierarchy

[Anonymous]

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

[theov]

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]

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]

@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

[Anonymous]

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