Issues with Calculate If forumulas in Power BI Desktop

[nmck86]

I am trying to build a % calculation based on a formula. So in the dataset that I am showing on the screenshot below you will see that 2 columns recommend and quality. I am trying to build a formula that does 2 different things. One I can accomplish easily. I need to count all the rows in the quality column that equal 10 and I use this formula:

Quality 10 Score Count = CALCULATE(COUNT(Sheet1[Quality]),Sheet1[Quality] =10)

That gives me the total count of all rows that have a 10 score. Once I complete that formula I need another one that looks at the recommended column and gives me a count of all the rows that equal 9&10 only if Quality was a 10. Any insight on how I can accomplish this. So if there are 8 rows = 10 within the "quality" column then count all the 9's and 10's in the corresponding "recommended" column. So of the 10 "quality" columns there are 5 "recommended columns" which will equal 50% score for quality at the score of 10. Not sure this makes sense but can provide other details as needed.

Sample Data

[v-yuta-msft]

Hi nmck86,

Would you please clarify the “So of the 10 "quality" columns there are 5 "recommended columns" which will equal 50% score for quality at the score of 10.”?

To achieve your requirement, you can create a measure and refer to DAX formula:

Quality 10 Score Count =

IF(MAX(Sheet1[Quality]) = 10,

    CALCULATE(COUNT(Sheet1[Quality]), FILTER(Sheet1, Sheet1[Quality] = 10), FILTER(Sheet1, Sheet1[Recommended] = 9 || Sheet1[Recommended] = 10)),

    BLANK())

The result is like below and you can refer to PBIX file:

https://www.dropbox.com/s/kdb54f5rx8kp7w1/For%20nmck86.pbix?dl=0

 

Best Regards,

Jimmy Tao

[Ashish_Mathur]

Hi,

Try this

=CALCULATE(COUNT(Sheet1[Quality]),Sheet1[Quality] =10,(Sheet1[Recommended] =10||Sheet1[Recommended] =9))

Hope this helps.