Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

How to create Groups by levels.

Hi All.

I want to display the worklever group by the average of Reporttocount. I have the columns Work lever and Reporttocount.


I made the groups below, but I'm not getting the right values.

WL1+ = WL1+WL2+WL3+WL4+WL5+WL6
WL2+ = WL2+WL3+WL4+WL5+WL6
WL3+ = WL3+WL4+WL5+WL6

Requirement:

1. Chart Type: Funnel
2. Horizontal axis - Work Level 1-6 plus additional section showing groupings. WL1+ is WL1-6, WL2+ is WL2-6, WL3+ is WL3-6

3. Calculate average of direct reports by Work Level

Please help me if I can present a visual similar to the one above.

 

 

 

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi Anonymous ,

    According to your descripton, I create a sample.

    Here's my solution.

    1. Create a new table "Level", then add an index column, sort the Level column by index column. Create relationship between fact table and the new table with level column.

    2. Create a calculated column.

    Column =
IF (
    RELATED ( 'Table'[Value] ) <> BLANK (),
    RELATED ( 'Table'[Value] ),
    SWITCH (
        'Level'[Level],
        "WL1+", AVERAGEX ( ALL ( 'Table' ), 'Table'[Value] ),
        "WL2+",
            AVERAGEX (
                FILTER ( ALL ( 'Table' ), 'Table'[Work Level] <> "WL1" ),
                'Table'[Value]
            ),
        "WL3+",
            AVERAGEX (
                FILTER ( ALL ( 'Table' ), NOT ( 'Table'[Work Level] IN { "WL1", "WL2" } ) ),
                'Table'[Value]
            )
    )
)

    Result:

    If we put it in a funnel chart, it will sort by value instead of level.

    We can instead use bar chart, get the result:

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

1 Reply

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi Anonymous ,

    According to your descripton, I create a sample.

    Here's my solution.

    1. Create a new table "Level", then add an index column, sort the Level column by index column. Create relationship between fact table and the new table with level column.

    2. Create a calculated column.

    Column =
IF (
    RELATED ( 'Table'[Value] ) <> BLANK (),
    RELATED ( 'Table'[Value] ),
    SWITCH (
        'Level'[Level],
        "WL1+", AVERAGEX ( ALL ( 'Table' ), 'Table'[Value] ),
        "WL2+",
            AVERAGEX (
                FILTER ( ALL ( 'Table' ), 'Table'[Work Level] <> "WL1" ),
                'Table'[Value]
            ),
        "WL3+",
            AVERAGEX (
                FILTER ( ALL ( 'Table' ), NOT ( 'Table'[Work Level] IN { "WL1", "WL2" } ) ),
                'Table'[Value]
            )
    )
)

    Result:

    If we put it in a funnel chart, it will sort by value instead of level.

    We can instead use bar chart, get the result:

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.