Determine percentage hitting a target

[Anonymous]

Hi,

I'm looking to determine the percentage of people hitting a given target, which I need to be able to dynamically calculate depending on the date hierarchy. Here's a simplified example of my needs and the current DAX I have to calculate part of this:

Data:

Date	Person	Appointment Status
01/05/2020	Rod	Complete
02/05/2020	Rod	No Show
03/05/2020	Rod	Complete
01/05/2020	Jane	Complete
02/05/2020	Jane	Complete
03/05/2020	Jane	Complete
01/05/2020	Freddy	No Show
02/05/2020	Freddy	No Show
03/05/2020	Freddy	Complete

DAX:

Success = CALCULATE(COUNTROWS(Data), Data[Appointment Status] = "Complete")

Total = COUNTROWS(Data)

Success Rate = DIVIDE([Success],[Total])

What I want to do:

Assuming a target of 50% success Rate, I want to acheive the following:

% of Count of Person where Success Rate >= 50%

So for the complete data set here it would be 66.67%, Rod and Jane (2 out of 3) hitting target.

For 02/05/2020 only it would be 33.3%, only Jane (1 out of 3) hitting target.

For reference, this is a simplified example explaining what I want to do, my actual data is millions of records.

I know this should be easy, but I'm going round in circles! Help

[amitchandak]

@Anonymous , GT need forced using all or all selected

Success = CALCULATE(COUNTROWS(Data), Data[Appointment Status] = "Complete")

Total = CALCULATE(COUNTROWS(Data),allselected(Data)) // OR CALCULATE(COUNTROWS(Data),allData))

Success Rate = DIVIDE([Success],[Total])

Please Watch/Like/Share My webinar on Time Intelligence: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
My YouTube Tips at: https://www.youtube.com/playlist?list=PLPaNVDMhUXGYrm5rm6ME6rjzKGSvT9Jmy

Appreciate your Kudos.

[elprobst]

@amitchandak  Thank you for this! Happened to be exactly what I needed.

[Anonymous]

@amitchandak Thanks for your reply. How does that work with the mentioned target of 50%? I need to calculate the % of people hitting the target, this wouldn't do that.

[Anonymous]

@amitchandak I've actually partially figured this out, but I have another problem... I oversimplified.

This DAX works based on determining the count of Person:

CALCULATE(COUNT(Person),Filter(Data,[Succcess Rate] >0.5))

And it's then straightforward to divide over the total count.

But I actually want to roll up to team and that I'm really struggling with. Every iteration of Filter just returns the Person count not grouped by Team:

Date	Team	Person	Appointment Status
01/05/2020	A	Rod	Complete
02/05/2020	A	Rod	No Show
03/05/2020	A	Rod	Complete
01/05/2020	A	Jane	Complete
02/05/2020	A	Jane	Complete
03/05/2020	A	Jane	Complete
01/05/2020	B	Freddy	No Show
02/05/2020	B	Freddy	No Show
03/05/2020	B	Freddy	Complete

[amitchandak · Accepted solution]

@Anonymous , percent of subtotal may help you there

https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390