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.
Dear community,
I am trying to build a KPI dash board that allows me to filter by relationship managers. My dataset looks like this:
Project ID | Revenue | Manager 1 | Manager 2 |
1 | 50000 | Peter | Jane |
2 | 30000 | Jane | |
3 | 8000 | Simone | |
4 | 10000 | Simone | Kate |
5 | 95000 | Fiona | |
6 | 68000 | Peter | Jane |
7 | 25000 | Kate |
What I am trying do present is this:
Manager | Revenue | Project count |
Peter | 118000 | 2 |
Simone | 18000 | 2 |
Jane | 148000 | 3 |
Fiona | 95000 | 1 |
Kate | 35000 | 2 |
The idea is that if a project is co-managed by two staff, both will get 100% credit for revenue and project count. But with the manager information spread over two columns a simple slicer just doesn't do it... What would be the next simplest solution to this?
Any guidance will be greatly appreicated.
Cheers,
Diana
Solved! Go to Solution.
Hi @DianaT ,
Here we go 🙂
Table 2 =
VAR k =
DISTINCT (
UNION ( DISTINCT ( 'Table'[Manager 1] ), DISTINCT ( 'Table'[Manager 2] ) )
)
VAR c =
ADDCOLUMNS (
FILTER ( k, 'Table'[Manager 1] <> BLANK () ),
"man", 'Table'[Manager 1]
)
VAR d =
ADDCOLUMNS (
c,
"Revene", CALCULATE (
SUM ( 'Table'[Revenue] ),
FILTER ( 'Table', 'Table'[Manager 1] = [man] )
)
+ CALCULATE (
SUM ( 'Table'[Revenue] ),
FILTER ( 'Table', 'Table'[Manager 2] = [man] )
),
"count", CALCULATE (
DISTINCTCOUNT ( 'Table'[Project ID] ),
FILTER ( 'Table', 'Table'[Manager 1] = [man] )
)
+ CALCULATE (
DISTINCTCOUNT ( 'Table'[Project ID] ),
FILTER ( 'Table', 'Table'[Manager 2] = [man] )
)
)
RETURN
SELECTCOLUMNS ( d, "Manager", [man], "Reven", [Revene], "Count_P", [count] )
Pbix as attached.
Hi @DianaT ,
Here we go 🙂
Table 2 =
VAR k =
DISTINCT (
UNION ( DISTINCT ( 'Table'[Manager 1] ), DISTINCT ( 'Table'[Manager 2] ) )
)
VAR c =
ADDCOLUMNS (
FILTER ( k, 'Table'[Manager 1] <> BLANK () ),
"man", 'Table'[Manager 1]
)
VAR d =
ADDCOLUMNS (
c,
"Revene", CALCULATE (
SUM ( 'Table'[Revenue] ),
FILTER ( 'Table', 'Table'[Manager 1] = [man] )
)
+ CALCULATE (
SUM ( 'Table'[Revenue] ),
FILTER ( 'Table', 'Table'[Manager 2] = [man] )
),
"count", CALCULATE (
DISTINCTCOUNT ( 'Table'[Project ID] ),
FILTER ( 'Table', 'Table'[Manager 1] = [man] )
)
+ CALCULATE (
DISTINCTCOUNT ( 'Table'[Project ID] ),
FILTER ( 'Table', 'Table'[Manager 2] = [man] )
)
)
RETURN
SELECTCOLUMNS ( d, "Manager", [man], "Reven", [Revene], "Count_P", [count] )
Pbix as attached.
This works beautifully! Thank you.
User | Count |
---|---|
91 | |
77 | |
71 | |
64 | |
58 |
User | Count |
---|---|
106 | |
97 | |
76 | |
63 | |
60 |