Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!!
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.
@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