How to sort row subtotal (average) into ranges and then count # in each range? (w/slicers)

[jaygilbert]

Hello,

Thanks for taking the time to read my post, any help would be appreciated!

I have a table of sales reps that shows shows the commission % from all of their accounts. The rowsubtotal is an average of those numbers, and is the number I want to use to group into a range and then count the amount in each range.

I am hoping to create a table that takes the sales rep's total average commission % (the row subtotal) and groups them by percentage range (see photo 2), and then counts how many sales reps have commission % within those ranges.

For example:

-Grouping average commission % = Sales Rep 102 has an average commission of 13.81%, which falls under the <15% group.

-Counting all sales reps = There are 150 sales reps that fall under <15%, 345 sales reps that fall under the 15-15.5% group, etc.

-Slicers would also be able to filter the accounts, so the overall commission % could change depending on the criteria.

*The highlighted numbers, which are the average of all their accounts, is the number I care most about.

*Sales reps would be sorted into a table like this, which is based on their overall average commission %

(Commission % is a column I created by dividing commission by sales)

Thank you!!

[Anonymous]

Hi @jaygilbert ,

I suggest you to create a Dimtable contains all range you need and then create a measure to count the sales sep.

Count = 
IF (
    HASONEVALUE ( DimTable[ColumnName] ),
    SWITCH (
        DimTable,
        "<15%",
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[Sales Sep Number] ),
                FILTER ( 'Table', [Commission %] < 0.15 )
            ),
        "15-15.5",
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[Sales Sep Number] ),
                FILTER ( 'Table', [Commission %] >= 0.15 && [Commission %] < 0.155 )
            ),
        "15.5-16",
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[Sales Sep Number] ),
                FILTER ( 'Table', [Commission %] >= 0.155 && [Commission %] < 0.16 )
            ),
        "16-16.5",
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[Sales Sep Number] ),
                FILTER ( 'Table', [Commission %] >= 0.16 && [Commission %] < 0.165 )
            ),
        "16.5-17",
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[Sales Sep Number] ),
                FILTER ( 'Table', [Commission %] >= 0.165 && [Commission %] < 0.17 )
            ),
        "17-17.5",
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[Sales Sep Number] ),
                FILTER ( 'Table', [Commission %] >= 0.17 && [Commission %] < 0.175 )
            ),
        "17.5-18",
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[Sales Sep Number] ),
                FILTER ( 'Table', [Commission %] >= 0.175 && [Commission %] < 0.18 )
            ),
        "18-18.5",
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[Sales Sep Number] ),
                FILTER ( 'Table', [Commission %] >= 0.18 && [Commission %] < 0.185 )
            ),
        "18.5-19",
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[Sales Sep Number] ),
                FILTER ( 'Table', [Commission %] >= 0.185 && [Commission %] < 0.19 )
            ),
        ">19",
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[Sales Sep Number] ),
                FILTER ( 'Table', [Commission %] >= 0.19 )
            )
    ),
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Sales Sep Number] ) )
)

Best Regards,
Rico Zhou

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

[amitchandak]

@jaygilbert , You can use a measure like

Averagex(Values(Table[Sales Rep]), [Commission %])

Measn simple avg above Sales Rep level

Avg of Sum : https://youtu.be/cN8AO3_vmlY?t=22980

You can also switch Subtotal with another measure using isinscope

How to Switch Subtotal and Grand Total in Power BI | Power BI Tutorials| isinscope: https://youtu.be/smhIPw3OkKA