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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
karimk
Helper III
Helper III

How to: Percentage of "YES" in a YES/NO Column

Hi.

I need some help creating a measure to calculate the amount of "YES" answers in a column with "YES" and "NO". I will use that result as a percentage in a monthly bar chart.

 

Any ideas? I´m totally new to DAX. I´ve tried DIVIDE (COUNT([column name] = 'YES'; COUNT([column name])) and variations.

 

 

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @karimk,

 

Try to do this measure:

 

% YES =
DIVIDE (
    CALCULATE ( COUNT ( Table[Column] ), Table[Column] = "YES" ),
    CALCULATE ( COUNT ( Table[Column] ), ALLSELECTED ( Table[Column] ) )
)

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

16 REPLIES 16
Anonymous
Not applicable

Hi,

 

I am trying to work out the service level for a call stats report. I have a column which says whether an incoming call was answered within 3 minutes and either states yes or no. On previous dahsboards on Excel I have used this formula =sum(Yes/(Yes+No). I have tried a lot of DAX formulas but have yet to find one that works. 

 

Does anyone have a solution to this?

 

Thanks,

CKrider
Regular Visitor

I'm using the code you provided to symbolize a thermometer gage. In the dashboard when "yes" is selected on a bar chart the thermometer reads 100%, which is fine. But when the "no" is selected it is >100% which is not fine. How do I adjust the calcluation to include the percent "no"? Or how do I trun off the thermometer gage when "no" is selected in a bar chart? 

 

Current % Meeting Goal =
DIVIDE (
CALCULATE ( COUNT ( Forward120Day[Projects Meeting Goal] ), Forward120Day[Projects Meeting Goal] = "YES" ),
CALCULATE ( COUNT ( Forward120Day[Projects Meeting Goal] ), ALLSELECTED ( Forward120Day[Projects Meeting Goal] ) )
)
 
MFelix
Super User
Super User

Hi @karimk,

 

Try to do this measure:

 

% YES =
DIVIDE (
    CALCULATE ( COUNT ( Table[Column] ), Table[Column] = "YES" ),
    CALCULATE ( COUNT ( Table[Column] ), ALLSELECTED ( Table[Column] ) )
)

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hello @MFelix 

I've used your recommended measure, which works, but i have another question.  I'm trying to use this on a year to year basis so i can show in a line or bar graph the differences between each month from last year to this year.  The data for the first year is good, but for the current year, the measure is calculating the previous year's percentage, which is in turn impacting my current year data (it's averaging last January and this January).  I also need the graph to stop when it reaches the latest data (i only have data until Feb. 14, 2020), rather than showing the previous year's percentage.  Can you assist?

 

Light blue is 2019 with all monthly data. Dark blue is 2020 that only has data to February

% Capture.PNG

 

Hi @UCHAdmin ,

 

Can you share the measure syntax you are using?

 

Also is the date being used on a calendar table or on the same table as the data to calculated the percentage?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @MFelix 

 

So i originally used two datasets, but i just combined them (Master) hoping i could use a single measure to do this.  I used the original measure you proposed to calculate "Yes" vs "No";

 

% Master = DIVIDE(CALCULATE(COUNT('FCOTS Master'[Delayed?]),'FCOTS Master'[Delayed?]="No"),CALCULATE(COUNT('FCOTS Master'[Delayed?]),ALLSELECTED('FCOTS Master'[Delayed?])))

 

but now the graph only calculates the first year data.  The data is laid out like the example below:

 

Date

Yes or No

1-jan-19

yes

1-jan-19

no

2-jan-19

no

2-jan-19

yes

3-jan-19

no

 

where there are multiple data for single days (the entire dataset covers all of 2019 and only the first two months of 2020)

 

Hi @UCHAdmin ,

 

What are you using for the two colours the year column of the date?

 

Based on my test using this measure with a year column as legeng gives the correct calculation.

 

Is it possible to share a sample file?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



hi @MFelix,

 

I tried using this same measure, but it returns only numerical values instead of a percentage. How do we have a percentage?

 

% YES =
DIVIDE (
    CALCULATE ( COUNT ( Table[Column] ), Table[Column] = "YES" ),
    CALCULATE ( COUNT ( Table[Column] ), ALLSELECTED ( Table[Column] ) )
)

 

What is the COUNTIF in powerBi?

 

regards,

Hi @ymirza,

 

You need to format the measure as a %.

 

The COUNTIF doesn't exists in DAX you have the CALCULATE that allows you to define several parameters/filter to have you main formula calculated in this case the IF part is the second part of the calculate where you have Table[Column]="YES"

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hi, in my situation, using that query produces the total number of yes's (1.26 K in my case). It is not converting it to a fraction!

Please help.

Awesome. thanks for quick reply @MFelix 

Anonymous
Not applicable

Another way is to create a calculated column that transforms "Yes" to 1 and "No" to 0. Then you only need to use the average and you will have the percentage of "Yes".

 

In my case, it's better, because there are some "Prefer not answer". For this option I set null, so it doesn't count on the final percentage.

Hi @Anonymous,

 

Although your option is also good as a best practice, if you can calculate a value based on a measure you shouldn't add a calculated column on your model, because it add's complexity and size to your files.

 

On the measures you can also created the "Yes", "No" and "null" so it will give you the correct answer and without adding to your data model since measure are calculated on need, so if you don't use on your visuals it the calculation is not made.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

You're right. In this case, a measure is the best option. 

 

I've only mentioned the column option, but in my case, I transform the column before loading to PowerBi, so I don't need any either option. 

Thank you very much! It worked, although I still don´t understand the code.

Hi @karimk,

The CALCULATE function is similar to SUMIFS in excel so basically you can do any formula and then.add filterd in the formula.

So what is done is count and filtering it just for the YES and this is the.first part of the division, the second part I use the ALLSELECTED to ignore all the context row filter and calculate the count of all.rows no.matter what filter there are in your visual so you get all yes and no.

Regards
MFelix

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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