Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
kkane
Regular Visitor

DAX KPI % Measure

Hi all,

 

I have a wait times dataset and am trying to create a measure that will show the % of compliance to the wait time target. 

 

Out of all the patients who had a first appointment (clock stop) in X Month, how many were below the wait time target.

 

So for example, if the target was <=20 days. Here is an small sample dataset anonymised:

 

ServiceTeamNHSNumberReferralDateAttendElapsed Working DaysElapsed Working WeeksElapsed DaysElapsedWeeksNumber
a1119/01/202312/10/202319138.2266381
b2231/01/202309/10/202318036011
c3328/03/202302/10/202313527188271
d4403/05/202325/10/202312625.2175251
e5524/04/202309/10/202312124.2168241
f1622/05/202318/10/202310821.6149221
g2708/06/202327/10/202310220.4141211
h3825/05/202310/10/20239919.8138201
i4905/06/202313/10/20239519130191
j51009/06/202317/10/20239318.6130191
k11120/06/202320/10/20238917.8122181
l21220/06/202320/10/20238917.8122181
m31320/06/202320/10/20238917.8122181
n41420/06/202320/10/20238917.8122181
o51509/06/202310/10/20238817.6123181
p11620/06/202318/10/20238717.4120181
q21709/06/202309/10/20238717.4122181

 

 

I thought I could do the following, but it's not working:

 

% KPI 1A = (CALCULATE(SUM('vw_PBI_WaitingTimes'[Elapsed Working Days]),('vw_PBI_WaitingTimes'[Elapsed Working Days] <= 20))/sum('vw_PBI_WaitingTimes'[Number]))

 

And also, more points if you can help me with a WHERE Type clause, WHERE Urgency = "Routine" AND Service KPI Group = "A"

 

 

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

HI @kkane,

I think the calculation logic is calculate the condition row count with all rows to get percentage instead of total elapsed days divide with row count. For this scenario, you can try to use the following measure formula if helps:

% KPI 1A =
CALCULATE (
    DIVIDE (
        CALCULATE (
            SUM ( 'vw_PBI_WaitingTimes'[Number] ),
            'vw_PBI_WaitingTimes'[Elapsed Working Days] <= 20
        ),
        SUM ( 'vw_PBI_WaitingTimes'[Number] ),
        -1
    ),
    ALLSELECTED ( 'vw_PBI_WaitingTimes' )
)

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

1 REPLY 1
v-shex-msft
Community Support
Community Support

HI @kkane,

I think the calculation logic is calculate the condition row count with all rows to get percentage instead of total elapsed days divide with row count. For this scenario, you can try to use the following measure formula if helps:

% KPI 1A =
CALCULATE (
    DIVIDE (
        CALCULATE (
            SUM ( 'vw_PBI_WaitingTimes'[Number] ),
            'vw_PBI_WaitingTimes'[Elapsed Working Days] <= 20
        ),
        SUM ( 'vw_PBI_WaitingTimes'[Number] ),
        -1
    ),
    ALLSELECTED ( 'vw_PBI_WaitingTimes' )
)

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors