Measure of a Measure

[brianmsbc]

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?

[v-juanli-msft]

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]

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 · Accepted solution]

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

[brianmsbc]

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]

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.