Dynamic Percentage calculation based on Selection

[Anonymous]

new to power bi so hoping some of you experts can help me.

the table below is highlighting kids shots on a goal, both using their right and left feet, and the number of times they failed to hit the target (broken down per foot). interested in building a percentage failure rate based on my selection, so for example, if i selected only person a i would want the failure rate (lets say on a card) to read 23% (i.e. (2+1)/(10+3) = 23%), however if i selected both person a and person b the failure rate should read 21% (i.e. (2+1+4+0)/(10+3+10+10)=21%). Any and all help would be much appreciated, thanks

[v-jiascu-msft]

Hi @Anonymous,

There are two options.

Option 1:

BySumx =
SUMX ( 'Table25', 'Table25'[Failed Left Foot] + 'Table25'[Failed Right Foot] )
    / SUMX (
        'Table25',
        'Table25'[Attempted Left Foot] + 'Table25'[Attempted Right Foot]
    )

Option 2:

BySum =
 ( SUM ( 'Table25'[Failed Left Foot] ) + SUM ( Table25[Failed Right Foot] ) )
    / ( SUM ( Table25[Attempted Left Foot] ) + SUM ( Table25[Attempted Right Foot] ) )

Finally, we can add them to a Card visual. A slicer will help to choose person.

These two formulas work under the context of 'Table25'[Person], so the result is dynamic.

 

Best Regards!

Dale

[Anonymous]

Hi Dale,

thank you for the response, thats works but can you confirm how you got the value in the card to be a percentage, as mine is showing as a decimal, i.e. 0.21. the option to change to percentage

under modelling - formatting - the percentage icon and Format options are greyed out (sorry cannot upload image as no option to do so?)

thanks,

dave

[Anonymous]

[v-jiascu-msft · Accepted solution]

@Anonymous

Hi Dave,

Click the measure first, then change the format.

Best Regards!

Dale