Forum Discussion

jmeccles's avatar
jmeccles
Helper I
9 years ago
Solved

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?

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    9 years ago

    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.

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

    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's avatar
      jmeccles
      Helper I
      8 years ago

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

      And thank you Anonymous. 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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