cancel
Showing results for
Did you mean:
onyebuchiboss34 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

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

Vvelarde Super Contributor

## Re: How to get percentage?

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

Proud to be a Datanaut!

12 REPLIES 12 Super User III

## Re: How to get percentage?

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

## 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 III

## 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

## Re: How to get percentage?

Try this.

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

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

## 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 tableTotalCounts =
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

## 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

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

Announcements #### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system. #### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge. #### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests! #### Power Platform Online Conference 