How to create Groups by levels.

[Anonymous]

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.

[v-yanjiang-msft · Accepted solution]

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.