Percentage Calculation based on Slicer

[jmeccles]

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?

[v-shex-msft]

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

[Greg_Deckler · Accepted solution]

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.

[jmeccles]

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.