cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nmck86 Member
Member

Issues with Calculate If forumulas in Power BI Desktop

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.

 

2018-03-01_21-57-03 rec qual.pngSample Data

2 REPLIES 2
Super User
Super User

Re: Issues with Calculate If forumulas in Power BI Desktop

Hi,

 

Try this

 

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

 

Hope this helps.

Community Support Team
Community Support Team

Re: Issues with Calculate If forumulas in Power BI Desktop

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

 1.PNG

Best Regards,

Jimmy Tao