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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

 

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

@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




Lima - Peru

View solution in original post

12 REPLIES 12
atif-hafeez
Helper I
Helper I

Try this. 

 

 

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

 

Anonymous
Not applicable

@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
Super User
Super User

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
Not applicable

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


@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
Not applicable

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

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
Not applicable

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 ?

@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




Lima - Peru
Anonymous
Not applicable

@Vvelarde this did the trick also. cheers.

 

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

 

Anonymous
Not applicable

Thanks @atif-hafeez , appreciate.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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