cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jmeccles Regular Visitor
Regular Visitor

Percentage Calculation based on Slicer

Hello, everyone!

I have a problem which I believe is very easy to solve; however, ...I haven't been able to so, I'm asking for your help.

Below, I have a table of values:

 

IDPatternYear
A23Y2016
D21N2016
A54Y2016
B55N2016
C09Y2017
K01M2017
H11N2017
B11N2017
V87N2017
T31M2017
A62Y2017

 

Now, "Pattern" has three (3) states (i.e. "Y", "N" and "M") and this is the dilemma:

If I create a column chart that is filtered to show the count of "Y" and "N" entries only, what measure can be built to compute the percentage of either "Y" or "N" to the total "Y" and "N" shown on the chart?

For example, if I add this measure to the Tooltip of the column chart, then hovering over the "Y" bar will show 4 / (4 + 5) = 44.4% and hovering over "N" will show 5 / (4 + 5) = 55.6%.

If I were to get more creative and add a slicer for "Year" and then select "2017", hovering would show 2 / (2 + 3) = 40% and 3 / (2 + 3) = 60% for the "Y" bar and "N" bar respectively.

Any ideas?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Percentage Calculation based on Slicer

In your "Count of ID" in your Values area, click the drop down and then Show as: Percent of Grand Total. Filter your visualization to get rid of M. You should have what you want.


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

Proud to be a Datanaut!


3 REPLIES 3
Highlighted
Super User
Super User

Re: Percentage Calculation based on Slicer

In your "Count of ID" in your Values area, click the drop down and then Show as: Percent of Grand Total. Filter your visualization to get rid of M. You should have what you want.


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

Proud to be a Datanaut!


Community Support Team
Community Support Team

Re: Percentage Calculation based on Slicer

Hi @jmeccles,

 

You can try to use below formula to get the percent of selected item count.

 

Measure:

Selected Percent =
VAR selected =
    IF ( HASONEVALUE ( Table[Pattern] ), VALUES ( Table[Pattern] ), BLANK () )
VAR allexceptM =
    COUNTX ( FILTER ( ALL ( Table ), [Pattern] <> "M" ), [ID] )
RETURN
    CALCULATE ( COUNT ( Table[ID] ), [Pattern] = selected )
        / allexceptM

2.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
jmeccles Regular Visitor
Regular Visitor

Re: Percentage Calculation based on Slicer

Thanks, @Greg_Deckler! It's simple and it works, just as I suspected.

And thank you @v-shex-msft. Your solution taught me a few things about the creation and use of variables in Power BI but it just wasn't what I was looking for.