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

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

 

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

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

Highlighted
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).

Highlighted
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
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors