KPI report: One project with two relationship managers

[DianaT]

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

[v-frfei-msft · Accepted 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.

[DianaT]

This works beautifully! Thank you.