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

[karimk]

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.

[Anonymous]

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]

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 · Accepted solution]

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

[UCHAdmin]

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

[MFelix]

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?

[UCHAdmin]

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)

[MFelix]

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?

[ymirza]

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,

[MFelix]

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

[Anonymous]

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.

[ymirza]

Awesome. thanks for quick reply @MFelix 

[Anonymous]

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.

[MFelix]

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

[Anonymous]

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. 

[karimk]

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

[MFelix]

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