cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 396 members 4,358 guests