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

 ID Pattern Year A23 Y 2016 D21 N 2016 A54 Y 2016 B55 N 2016 C09 Y 2017 K01 M 2017 H11 N 2017 B11 N 2017 V87 N 2017 T31 M 2017 A62 Y 2017

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

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

Proud to be a Datanaut!

3 REPLIES 3
Highlighted
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.

Proud to be a Datanaut!

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

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