Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nmck86
Post Patron
Post Patron

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.

 

Sample DataSample Data

2 REPLIES 2
v-yuta-msft
Community Support
Community Support

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

Ashish_Mathur
Super User
Super User

Hi,

 

Try this

 

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

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.