Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
jaygilbert
Regular Visitor

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

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.

jaygilbert_0-1698079928579.png

 

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

jaygilbert_1-1698078714486.png

 

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

 

Thank you!!

 

2 REPLIES 2
Anonymous
Not applicable

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
Super User
Super User

@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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors