cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
onyebuchiboss34 Frequent Visitor
Frequent Visitor

How to get percentage?

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?
2 ACCEPTED SOLUTIONS

Accepted Solutions
atif-hafeez Regular Visitor
Regular Visitor

Re: How to get percentage?

 

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

Edit:

Slightly modified the TotalifYes variable Smiley Wink

 

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())

 

View solution in original post

Vvelarde Super Contributor
Super Contributor

Re: How to get percentage?

@onyebuchiboss34 

 

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




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

Proud to be a Datanaut!




View solution in original post

12 REPLIES 12
Super User
Super User

Re: How to get percentage?

Hi @onyebuchiboss34 

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%

  

onyebuchiboss34 Frequent Visitor
Frequent Visitor

Re: How to get percentage?

@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 ?

Super User
Super User

Re: How to get percentage?


@onyebuchiboss34 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 

atif-hafeez Regular Visitor
Regular Visitor

Re: How to get percentage?

Try this. 

 

 

CountofHeards = COUNTA('Table'[Heardofus?])
TotalCounts = 
CALCULATE
(
    [CountofHeards],
    all('Table'[Heardofus?])
)
% heard = [CountofHeards]/[TotalCounts]

 

onyebuchiboss34 Frequent Visitor
Frequent Visitor

Re: How to get percentage?

@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.
Highlighted
onyebuchiboss34 Frequent Visitor
Frequent Visitor

Re: How to get percentage?

@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 Regular Visitor
Regular Visitor

Re: How to get percentage?

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]

 

onyebuchiboss34 Frequent Visitor
Frequent Visitor

Re: How to get percentage?

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 ?

atif-hafeez Regular Visitor
Regular Visitor

Re: How to get percentage?

 

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

Edit:

Slightly modified the TotalifYes variable Smiley Wink

 

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())

 

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,059)