Get the Rank for the qualifying conditions

[Kalaivani]

Hi All,

I have a requirement where I need to give color coding for measures in a table like

When Target

>=90% then green,

<90% for the first time, then yellow

<90% after first time, then red

I created a measure to find the missed targets as below. 

MissedTarget =
CALCULATE(COUNTROWS(FILTER(SUMMARIZE('Template Expectations','Template Expectations'[PostPeriod],"TemplateExpValue"[TemplateExpectations]),[TemplateExpectations] <0.9)))

But actually I want this to be like 1, 2, 3 based on the order of postperiod. So that I can color code yellow for Missed Target = 1 and red for MissedTarget >1

So the expected output has to be 1, 2, 3 for ID 66159 where MissedTarget is 1 and 1, 2 for ID 154179 where MissedTarget is 1

Thanks so much for your help!!!!

[Anonymous]

Thanks for the replies from danextian, powerbiexpert22 and Ashish_Mathur.

Hi @Kalaivani ,

If you also need to show the exact number of times less than 90% occurs, you can create the following measure:

Count_Less_Than_90 = CALCULATE(COUNTROWS('Table'),FILTER('Table',[Measure]<0.9))

Count_Less_Than_90_Cumulative = IF([Count_Less_Than_90]=1,SUMX(FILTER(ALLEXCEPT('Table','Table'[ID]),'Table'[Month]<=MAX('Table'[Month])),[Count_Less_Than_90]))

Set background color rules:

Result:

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[Kalaivani]

Hi @Anonymous 

This is cool!!!!!!! Thanks so much. It's working.I have one more condition where when the values are blank for any month, it should show as white. How to achieve that? Thanks again!

[Anonymous · Accepted solution]

Hi @Kalaivani ,

Use the following formula as the basis for setting the background color:

NewRule = IF(COUNTROWS(FILTER(ALL('Table'),'Table'[ID]=MAX('Table'[ID])&&[Measure]=BLANK()))>0,0.1,IF([Count_Less_Than_90]=1,SUMX(FILTER(ALLEXCEPT('Table','Table'[ID]),'Table'[Month]<=MAX('Table'[Month])),[Count_Less_Than_90])))

Result:

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[Kalaivani]

Hi @Anonymous Thanks so much for the answer

[powerbiexpert22]

Hi @Kalaivani ,

please see below solution

https://drive.google.com/file/d/1UeRq-F9aeUTJ2uOyHMACa_pSXGBC2e0G/view?usp=drive_link

[Kalaivani]

Hi @powerbiexpert22 I have a case where the second month Aug 2024 value is less than 90% and for July 2024 the value is greater than 90%. In that case, my rank is showing as 2 for the first case where value is <90%
The underlined blue row for Aug 2024 for the ID 180440 has to be 1. Thanks again for your help!!

[Kalaivani]

Hi @powerbiexpert22 Thanks so so much for your help! It's working. 

[danextian]

Hi @Kalaivani 

Can you pleast post a workable sample data (not an image), your expected result from that and the reasoning behind?

[Kalaivani]

Hi @danextian I am not able to attach files in here. Could you please help me with calculating rank for the measure MissedTarget. I think that will solve the solution

[danextian]

It is hard to provide a solution when there is no sample data to work on. You may post a url of the sanitized copy of the pbix saved in the cloud.

[Kalaivani]

Hi @danextian i uploaded the power bi file with sample data in the drive. could you please access it and send me request access so that i can provide access to you to download the file. I am not able to upload to the cloud.

Thanks so much for your help!!

https://drive.google.com/drive/homehttps://drive.google.com/file/d/1fj6fStKlJRWC6acJH3BwXY68flJaIkr0...

[danextian]

[Ashish_Mathur]

Hi,

Access Denied message.  Check the link again.