Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello Everyone,
I've hit a wall when trying to produce a fairly complex percentage calculation. The data is confidential so I will provide an example table below.
Client ID | Goal | Circumstance |
001 | X | |
001 | X | |
001 | X | |
001 | X | |
001 | ||
002 | X | |
002 | X | |
002 | X | |
002 | X | |
003 | X | |
003 | X | |
003 | X | |
003 | X | |
003 | X | |
003 | X |
So, my dillema is this. I need to calculate the percentage of total DISTINCT Client IDs that have greater than or equal to two non-blank rows in both "goals" AND "circumstances". So for the above table, the result would need to be 66.6~% - counting client 001 once, 003 once and exluding 002 all together.
Happy to provide further info if needed, and thanks in advance for any help you are able to provide!
Solved! Go to Solution.
Hi @Flawn
Please refer to attached file with the solution
% Goal > Circumstance =
VAR AllSelectedClients =
VALUES ( Data[Client ID] )
VAR RequiredClients =
SUMX (
AllSelectedClients,
CALCULATE (
VAR CountGoal = COUNTROWS ( FILTER ( Data, NOT ISBLANK ( Data[Goal] ) ) )
VAR CountCircumstance = COUNTROWS ( FILTER ( Data, NOT ISBLANK ( Data[Circumstance] ) ) )
RETURN
IF ( CountGoal >= 2 && CountCircumstance >= 2, 1, 0 )
)
)
RETURN
DIVIDE ( RequiredClients, COUNTROWS ( AllSelectedClients ) )
Hi @Flawn
Please refer to attached file with the solution
% Goal > Circumstance =
VAR AllSelectedClients =
VALUES ( Data[Client ID] )
VAR RequiredClients =
SUMX (
AllSelectedClients,
CALCULATE (
VAR CountGoal = COUNTROWS ( FILTER ( Data, NOT ISBLANK ( Data[Goal] ) ) )
VAR CountCircumstance = COUNTROWS ( FILTER ( Data, NOT ISBLANK ( Data[Circumstance] ) ) )
RETURN
IF ( CountGoal >= 2 && CountCircumstance >= 2, 1, 0 )
)
)
RETURN
DIVIDE ( RequiredClients, COUNTROWS ( AllSelectedClients ) )
At a cursory look, this appears to be returning the results we would expect, but i need to do some testing to be sure. Kudos for now, will return and accept as solution once testing is complete if it proves to be accurate.
Hi,
You could create the measure like below :
I really like this solution theoretically - I apprecaite how it allows for flexible adjustment via variables - but for whatever reason I cannot get it to work in practice. It always returns all results as "no" and even then, I struggle to actually utilize the variables to construct a functional percentage calculation/division formula. This would be my preferred of the presented solutions if I could get it to work, so for now i'll kudos it in hopes of some fixes!
Hi @Flawn ,
Assuming your table looks like this.
Client ID | Goal | Circumstance |
1 | X | |
1 | X | |
1 | X | |
1 | X | |
1 | ||
2 | X | |
2 | X | |
2 | X | |
2 | X | |
3 | X | |
3 | X | |
3 | X | |
3 | X | |
3 | X | |
3 | X |
Step 1 : Create a custom table
Table =
ADDCOLUMNS (
SUMMARIZE ( Sheet1, Sheet1[Client ID] ),
"Count of Goal", CALCULATE ( COUNT ( Sheet1[Goal] ), Sheet1[Goal] <> "" ),
"Count of Circumstances", CALCULATE ( COUNT ( Sheet1[Circumstance] ), Sheet1[Circumstance] <> "" )
)
Step 2: In the new table , create a calculated column
% =
DIVIDE (
CALCULATE ( SUM ( 'Table'[Count of Goal] ), 'Table'[Count of Goal] >= 2 ),
COUNT ( 'Table'[Count of Goal] )
)
Step 3 : Output
Hope that helps
Dax_Noob