cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
brianmsbc Frequent Visitor
Frequent Visitor

Measure of a Measure

I have a list of Categories (Providers) that are composed of Subcategories . Each SubCategory has a star rating which is a measure... using the Pass Rate (Sum of Numerator)/(Sum of Denominator) compared to the Star Rating (1 through 5) goals (Different for each SubCategory). I was able to create that measure (Star Rating) correctly with no issue.

 

However, at the Parent level (the blotted out item), it is using the same logic as the Child, which comes out to 1. I need the Parent to give me the weighted (0, 1, or 3 based on subcategory) average Star Rating .

 

I cant figure out how to accomplish this does anyone have any suggestions to point me in the right direction?

 

Snipit02142019.PNG

 

1 ACCEPTED SOLUTION

Accepted Solutions
brianmsbc Frequent Visitor
Frequent Visitor

Re: Measure of a Measure

Basically ALLEXCEPT doesnt do what i thought it did. I took out the ALLEXCEPT (both of them) statement and it worked perfectly.

View solution in original post

5 REPLIES 5
Community Support Team
Community Support Team

Re: Measure of a Measure

Hi @brianmsbc

You could use "IF" and hasonevalue function to have different rules for child and parent fields.

 

If you could solve it by yourself, 

Please show me how you create the measure,

which is the child and which is the parent column from the screenshot,

For this, "I need the Parent to give me the weighted (0, 1, or 3 based on subcategory) average Star Rating ",

How do i define which weighted(0,1,3) to use?

 

Best Regards

Maggie

 

brianmsbc Frequent Visitor
Frequent Visitor

Re: Measure of a Measure

The HASONEVALUE suggestion looks as if it would solve half of the problem... because the final solution definitely needs two seperate equations, one for the parent and one for the child. Using the function you provided seems as if it would seperate the two equations correctly. I am still having trouble figuring out the equation for the Parent however.

brianmsbc Frequent Visitor
Frequent Visitor

Re: Measure of a Measure

Replying to myself here... but the reason that the Parent formula is tough for me is that it HAS to consider each Child measure as its own group. The formula HAS to be the average of childs (Weight * StarRating). The issue is that i dont know how to create a measure of a measure and i dont know how to tell it to group it by Child. 

brianmsbc Frequent Visitor
Frequent Visitor

Re: Measure of a Measure

I figured this problem out 90% of the way... however there is one part of it that is not working. Here is the final code that kinda works:

 

StarRating = SUMX(VALUES('Table'[ChildName]),CALCULATE(IF(SUM('Table'[Denominator])=0,0,MAX('Table'[Weight])* 
IF((SUM('Table'[Numerator])/SUM('Table'[Denominator]))>=MAX('Table'[Star5]),5,IF((SUM('Table'[Numerator])/SUM('Table'[Denominator]))>=MAX('Table'[Star4]),4,IF((SUM('Table'[Numerator])/SUM('Table'[Denominator]))>=MAX('Table'[Star3]),3,IF((SUM('Table'[Numerator])/SUM('Table'[Denominator]))>=MAX('Table'[Star2]),2,1)))
)),ALLEXCEPT('Table','Table'[ChildName],'Table'[ParentName])))/SUMX(VALUES('Table'[ChildName]),CALCULATE(MAX('Table'[Weight]),ALLEXCEPT('Table','Table'[ParentName],'Table'[ChildName])))

 

I had to clean it up a little for presentation, so the parenthesis may not be pefect. Basically I took the calculation i was using to determing StarRating at the Child level (that is huge nested IF statement in the middle of the code).

I needed to group by the Parent and Child and then Sum the results. However, the entire report is filterable by HMO, RPPO, and/or LPPO. There has been no issue with the report abiding by this filter... however the above code seems to ignore this filter. Any idea what is going on?

 

Basically im asking if my suspicions are correct that ALLEXCEPT() overrides any filters (filters via the Slicer Visual i mean).

brianmsbc Frequent Visitor
Frequent Visitor

Re: Measure of a Measure

Basically ALLEXCEPT doesnt do what i thought it did. I took out the ALLEXCEPT (both of them) statement and it worked perfectly.

View solution in original post

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors