How to get percentage?

[Anonymous]

This seems a bit silly, but my measures on percentage is showing odd figures. I have a column, with a "Yes" or "No" values. I did the following to extract the number of "Yes" values:

* Yes  Measure = (CALCULATE(DISTINCTCOUNT(Merge1[_msfpvalueid]),Merge1[heard of us?]="Yes"))

Then to get the percentage of "Yes" values in the column, i did the following:

* Yes % =  [Yes  Measure]/COUNTA(Merge1[[heard of us?] ), with percenatge formate.

It returns the right percentage, but when I select NO in a slicer, it returns something like 385%, instead of the expected  "0" or blank.

What I'm I doing wrong here?

[atif-hafeez · Accepted solution]

Here you go my friend. Please accept this as correct if this meets your requirements

Edit:

Slightly modified the TotalifYes variable 

CountofYesHeard = CALCULATE(COUNTA('Table'[Heardofus?]), 'Table'[Heardofus?] = "Yes") 

TotalCounts = 
VAR choice = SELECTEDVALUE('Table'[Heardofus?])
VAR TotalifYes = [CountofYesHeard],
VAR Total = 
CALCULATE
(
    COUNTA('Table'[Heardofus?]),
    all('Table'[Heardofus?])
)
RETURN
SWITCH
(
    choice,
    "No",BLANK(),
    "Yes", TotalifYes,
    Total
)

% heard = DIVIDE([CountofYesHeard],[TotalCounts], BLANK())

[atif-hafeez]

Try this. 

CountofHeards = COUNTA('Table'[Heardofus?])

TotalCounts = 
CALCULATE
(
    [CountofHeards],
    all('Table'[Heardofus?])
)

% heard = [CountofHeards]/[TotalCounts]

[Anonymous]

@atif-hafeez this line - 

CountofHeards = COUNTA('Table'[Heardofus?])

I am trying to extract all the values that is equals to "yes" nit count all the values. When i did  this 

(CALCULATE(DISTINCTCOUNT(Merge1[itemvalueid]),Merge1[Hreardofus?]="Yes")), in place of the above, i keep getting 100%, when either YES or NO is selcted in the slicer.

[AlB]

Hi @Anonymous 

Because of the Merge1[heard of us?] = "Yes" filter,  [Yes Measure] always measures the number of "Yes", regardless of the value on the slicer. On the denominator of [Yes %]  you are counting only the rows with "No" if that's what you have in the slicer and not the total. So it is perfectly possible to get a value above 100%

[Anonymous]

@AlB in the slicer, there is the YES and NO option, when i select "Yes", the measure turns to 100%(expected), but when i select NO, it returns over 100%, instead of a black. The denopmnator of the YES %, i did COUNTA(Merge[heard of us]), this should count all rows regardless.

Can you help me out with this ?

[AlB]

---

@Anonymous wrote:

@AlB in the slicer, there is the YES and NO option, when i select "Yes", the measure turns to 100%(expected), but when i select NO, it returns over 100%, instead of a black. The denopmnator of the YES %, i did COUNTA(Merge[heard of us]), this should count all rows regardless.

Nope. It shouldn't.  COUNTA(Merge[heard of us]) is directly affected by any active filter so if you're filtering for "No" with the slicer, it will count the Noes only. When you filter for "Yes", you get 100% because numerator always counts the number of Yes, irrespective of the slicer value, and the denominator counts whatever you have in the slicer, Yes in this case, so 100%

If you want to count all rows irrespective of the value in the slicer and thus filter context, you need an ALL with the CALCULATE:

CALCULATE( COUNTA(Merge[heard of us]), ALL(Merge[heard of us]) )

 assuming Merge[heard of us] is the only column you are filtering on 

[Anonymous]

@AlB following your calculation, it gives a static value of 74% regardless of the value in the slicer.

This is my case here, I have a column of something like the below:

A  B     

1  yes   

2  yes

3  No

4  yes

5 No

All I want to do here is, get the percentage of "Yes"  values, when the value of the slicer is equals to "NO", it shld be blank and when it is "Yes", it should be equals to 100%.  This seems easy, but power bi being Power bi, its giving me something else.

[atif-hafeez]

Ok, give this a try

CountofHeards = 
var choice = SELECTEDVALUE('Table'[Heardofus?])
RETURN
if
(
    choice = "No", 
    BLANK(),
    CALCULATE(COUNTA('Table'[Heardofus?]), 'Table'[Heardofus?] = "Yes") 
    
)

//Using this in % calculation should give you a percentage of yes values in the whole table
TotalCounts = 
CALCULATE
(
    COUNTA('Table'[Heardofus?]),
    all('Table'[Heardofus?])
)

//Using this in % calculation should give you 100% everytime you select Yes
TotalCounts = 
CALCULATE
(
    [CountofHeards],
    all('Table'[Heardofus?])
)

% heard = [CountofHeards]/[TotalCounts]

[Anonymous]

hi @atif-hafeez , we are almost there,  I used your images 1, 3 and 4. now regardless of any selection in the slicer, its always showing 100% and when i select "NO", it returns a blank. I want it showing the percentage of yes, when nothing is selected, when "Yes" is selected, it should show 100% and when "NO" is selected, i should get blank. Can this done ?

[Vvelarde · Accepted solution]

@Anonymous 

Hi, try with this:

CountYes = CALCULATE(COUNTA(Table1[A]),FILTER(Table1,Table1[B]="yes"))

%Yes of Total = DIVIDE([CountYes],CALCULATE(COUNTA(Table1[A]),ALLSELECTED(Table1)))

Regards

Victor

Lima - Peru

[Anonymous]

@Vvelarde this did the trick also. cheers.

[atif-hafeez · Accepted solution]

Here you go my friend. Please accept this as correct if this meets your requirements

Edit:

Slightly modified the TotalifYes variable 

CountofYesHeard = CALCULATE(COUNTA('Table'[Heardofus?]), 'Table'[Heardofus?] = "Yes") 

TotalCounts = 
VAR choice = SELECTEDVALUE('Table'[Heardofus?])
VAR TotalifYes = [CountofYesHeard],
VAR Total = 
CALCULATE
(
    COUNTA('Table'[Heardofus?]),
    all('Table'[Heardofus?])
)
RETURN
SWITCH
(
    choice,
    "No",BLANK(),
    "Yes", TotalifYes,
    Total
)

% heard = DIVIDE([CountofYesHeard],[TotalCounts], BLANK())

[Anonymous]

Thanks @atif-hafeez , appreciate.