Forum Discussion

DianaT's avatar
DianaT
Icon for Helper I rankHelper I
6 years ago
Solved

KPI report: One project with two relationship managers

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 IDRevenueManager 1Manager 2
150000PeterJane
230000Jane 
38000Simone 
410000Simone Kate
595000Fiona 
668000Peter Jane
725000Kate 

 

What I am trying do present is this:

 

Manager RevenueProject count
Peter1180002
Simone180002
Jane1480003
Fiona950001
Kate350002

 

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

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    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.

     

2 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      DianaT
      Icon for Helper I rankHelper I
      6 years ago

      This works beautifully! Thank you.