Forum Discussion
Helper IIISUMX Formula with conditional SUMIF
Hi - I am trying to calculate 'points' for a sales incentive where points are awarded per number of products a customer purchases.
The totals - which work when calculated against a customner - does not total up correctly in a state summary. See screenshot below. Do i somehow need to incorporat ethe state into the SUMX formula?
Thanks
4 Replies
- amitchandak
Super User
AndySmith , This because data is grouped to customer level and then do the calculation. So you grand total are calculated again using customer level
For state you might have to do
Points = SUMX(VALUES('BrandPrint'[State Name]),CALCULATE(IF([New Listings Count]=0,0,IF(AND([New Listings Count]>=1,[Product Count]=4),12,[Product Count]*2))))
AndySmithThanks for looking into it. Sadly I have tried that already and it doesnt seem to change anything.
I have attached a link to a pbix with sample data. You can see that the Customer summary adds up but the state and salesperson tables do not total correctly. Hopefully someone can make this work!
https://www.dropbox.com/s/l7xi0rdgr5x2vdj/IncentiveSampleData.pbix?dl=0
- AndySmith
Hoping to bump this to see if there may be a solution?
amitchandak - do you have any thoughts?
Thanks
- Ashish_Mathur
Hi,
In the first visual, the Points add up to 406 whereas they should add upto 408. This measure gives the result as 408
Measure = SUMX(Targets,[Points])Hope this helps.
