Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
107 | |
105 | |
87 | |
61 |