Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a chart that calculates bonus points for accounts that reach certain thresholds for each sales rep.
2000 points for accounts over 10,000
4000 points for accounts over 25,000
6000 points for accounts over 50,000
Here's an image of the graph:
The graph shows bonus points for almost every sales rep - when in reality only two reps have earned bonus points - grid image:
I have tried to tackle this in a previous post:
I still have a totaling issue in the reports despite trying multiple solutions.
The issue is it totals columns that shouldn't be totaled which is how sales reps that don't have any bonus points are showing as they do in the chart at the top.
I have to get this report right so that's why I'm taking another stab at it - to see if there's a new way to look at this and handle the odd totaling. 🙂
Here's the script for the bonus points:
IF('SALES DETAILS'[New Customer Points Per Dollar] >= 10000 && 'SALES DETAILS'[New Customer Points Per Dollar] <= 24999, 2000, IF('SALES DETAILS'[New Customer Points Per Dollar] >= 25000 && 'SALES DETAILS'[New Customer Points Per Dollar] <= 49999, 6000, IF('SALES DETAILS'[New Customer Points Per Dollar] >= 50000, 12000, 0) ))
I have two reps that have earned bonus points: If I filter by rep the filter - the total seems to be fine -
When I show both reps the total is messed up.
In the chart It starts showing points that aren't really there. The bars with asterisks are the only ones that are "legit".
ANY help with this would be greatly appreciated! Like I said - this is a second attempt at this - just hoping to either see where I messed up in my previous attempts or see if I can get a new perspective on how to correct this issue. Thanks in advance!! 😄
EDIT: I have also tried this: http://www.powerpivotpro.com/2012/03/subtotals-and-grand-totals-that-add-up-correctly/.
Here is the script I use and I still have the same issue:
Bonus Points PRO Customer = IF(COUNTROWS(VALUES('Customer'[Customer Name]))=1, [Bonus Points Measure], SUMX(VALUES(Customer[Customer Name]), [Bonus Points Measure]))
Solved! Go to Solution.
Woooohoooo! 😄
I'm not sure why this worked - but I got it to work properly!! @Sean @kcantor
Using a combination of what you all suggested in a previous post - all I did was change which column I was pointing to (referencing the customer) and it worked... I have no idea what the difference is. But I don't care at this point. 🙂
Here's the final script and results!! 😄
This:
#Correct Script Bonus Points PRO Customer = IF(COUNTROWS(VALUES('Customer'[ONLY Current Year Customers]))=1, [Bonus Points Measure], s SUMX(VALUES(Customer[ONLY Current Year Customers]), [Bonus Points Measure]))
Replaced this:
Bonus Points PRO Customer = IF(COUNTROWS(VALUES('Customer'[Customer Name]))=1, [Bonus Points Measure], SUMX(VALUES(Customer[Customer Name]), [Bonus Points Measure]))
And gave this :
@heathernicole EDIT: Already solved! Great! I guess I was posting at about the same time!
You can try this...
Measure2 Customer = IF ( ISFILTERED ( 'Customer'[Customer Name] ), [Bonus Points Measure], SUMX ( VALUES ( Customer[Customer Name] ), [Bonus Points Measure] ) )
Also try this...
Bonus Points PRO2 Customer = IF ( ISFILTERED ( 'SALES DETAILS'[Rep Code] ), [Bonus Points MEASURE], SUMX ( VALUES ( 'SALES DETAILS'[Customer] ), [Bonus Points MEASURE] ) )
As you can see all Measures I've given work... Fingers crossed...
@heathernicole EDIT: Already solved! Great! I guess I was posting at about the same time!
You can try this...
Measure2 Customer = IF ( ISFILTERED ( 'Customer'[Customer Name] ), [Bonus Points Measure], SUMX ( VALUES ( Customer[Customer Name] ), [Bonus Points Measure] ) )
Also try this...
Bonus Points PRO2 Customer = IF ( ISFILTERED ( 'SALES DETAILS'[Rep Code] ), [Bonus Points MEASURE], SUMX ( VALUES ( 'SALES DETAILS'[Customer] ), [Bonus Points MEASURE] ) )
As you can see all Measures I've given work... Fingers crossed...
@Sean - lol Thanks so much for giving this another go! This solution looks great. I may make a backup copy of my report and give it a go. For now I'm leaving it so as not to break it. lol
THANK YOU!!!!
Woooohoooo! 😄
I'm not sure why this worked - but I got it to work properly!! @Sean @kcantor
Using a combination of what you all suggested in a previous post - all I did was change which column I was pointing to (referencing the customer) and it worked... I have no idea what the difference is. But I don't care at this point. 🙂
Here's the final script and results!! 😄
This:
#Correct Script Bonus Points PRO Customer = IF(COUNTROWS(VALUES('Customer'[ONLY Current Year Customers]))=1, [Bonus Points Measure], s SUMX(VALUES(Customer[ONLY Current Year Customers]), [Bonus Points Measure]))
Replaced this:
Bonus Points PRO Customer = IF(COUNTROWS(VALUES('Customer'[Customer Name]))=1, [Bonus Points Measure], SUMX(VALUES(Customer[Customer Name]), [Bonus Points Measure]))
And gave this :
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |