Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Given the following table and data:
Group Task
===== ====
XYZ A
XYZ C
XYZ D
XYZ E
PDQ B
PDQ C
PDQ D
PDQ E
LUV G
LUV H
I'd like to create a Matrix showing the count of the overlap of two selected groups (XYZ, PDQ) assigned to the same task:
Group Group
Task XYZ PDQ Overlap
A 1 0 1
B 0 1 1
C 1 1 2
D 1 1 2
E 1 1 2
------------------ ===========
Overlap: 3
And then have a card (or other visual) displaying the Percent overlap between the two selected groups:
Group XYZ: 75% ( 3 overlapping out of 4)
Is this possible in Power BI?
In Excel I can just do a CountIf() on the Overlap column, but I'm not sure how to achieve this in Power BI
Solved! Go to Solution.
Hi @NuTek,
You can use FIRSTNONBLANK/LASTNONBLANK function to get the first/last selected group.
First Selected Group = FIRSTNONBLANK(Table1[Group],1)
The formula below is for your reference.
% Overlap for first Selected Group = VAR firstSelectedGroup = FIRSTNONBLANK ( Table1[Group], 1 ) VAR count_of_overlap = CALCULATE ( DISTINCTCOUNT ( Table1[Task] ), FILTER ( Table1, CALCULATE ( COUNT ( Table1[Task] ), ALLSELECTED ( Table1[Group] ) ) = 2 ) ) + 0 VAR count_of_task_XYZ = CALCULATE ( DISTINCTCOUNT ( 'Table1'[Task] ), 'Table1'[Group] = firstSelectedGroup ) + 0 RETURN DIVIDE ( count_of_overlap, count_of_task_XYZ, 0 )
Regards
Shouldn't your example give 3/5 or 60% ?
If so this Measure should do it...
% Overlap = DIVIDE ( CALCULATE ( DISTINCTCOUNT ( 'Table'[Group] ), ALLEXCEPT ( 'Table', 'Table'[Task] ) ), DISTINCTCOUNT ( 'Table'[Task] ), 0 )
Thanks for the fast reply 🙂
The number I'm looking for is the number of "2"'s in the "Overlap" column divided by the number of rows with a "1" in the Group columns. ex: Group XYZ has 4 tasks (A,C,D,E) with 3 tasks appearing in both Group XYZ and PDQ (there are 3 columns in Overlap with a "2")
Will try yo play with the solution you provided.
Thanks!
Hi @NuTek,
Could you try using the formula below to create a measure, then just show the measure on the Card visual to see if it works?
% Overlap for XYZ = VAR count_of_overlap = CALCULATE ( DISTINCTCOUNT ( Table1[Task] ), FILTER ( Table1, CALCULATE ( COUNT ( Table1[Task] ), ALLSELECTED ( Table1[Group] ) ) = 2 ) ) + 0 VAR count_of_task_XYZ = CALCULATE ( DISTINCTCOUNT ( 'Table1'[Task] ), 'Table1'[Group] = "XYZ" ) + 0 RETURN DIVIDE ( count_of_overlap, count_of_task_XYZ, 0 )
Regards
Thanks for the formula. The results look goodin your example... Is there a way to not have to hardcode the "XYZ" and replace that with the selected value?
Hi @NuTek,
You can use FIRSTNONBLANK/LASTNONBLANK function to get the first/last selected group.
First Selected Group = FIRSTNONBLANK(Table1[Group],1)
The formula below is for your reference.
% Overlap for first Selected Group = VAR firstSelectedGroup = FIRSTNONBLANK ( Table1[Group], 1 ) VAR count_of_overlap = CALCULATE ( DISTINCTCOUNT ( Table1[Task] ), FILTER ( Table1, CALCULATE ( COUNT ( Table1[Task] ), ALLSELECTED ( Table1[Group] ) ) = 2 ) ) + 0 VAR count_of_task_XYZ = CALCULATE ( DISTINCTCOUNT ( 'Table1'[Task] ), 'Table1'[Group] = firstSelectedGroup ) + 0 RETURN DIVIDE ( count_of_overlap, count_of_task_XYZ, 0 )
Regards
You can always hardcode XYZ in the formula
% Overlap for XYZ = DIVIDE ( CALCULATE ( DISTINCTCOUNT ( 'Table'[Group] ), ALLEXCEPT ( 'Table', 'Table'[Task] ) ), CALCULATE ( DISTINCTCOUNT ( 'Table'[Task] ), 'Table'[Group]="XYZ" ), 0 )
Hope this helps!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |