SUMX Formula with conditional SUMIF

[AndySmith]

Hi - I am trying to calculate 'points' for a sales incentive where points are awarded per number of products a customer purchases. 

Points = SUMX(VALUES('BrandPrint'[Customer Name]),CALCULATE(IF([New Listings Count]=0,0,IF(AND([New Listings Count]>=1,[Product Count]=4),12,[Product Count]*2))))

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

[AndySmith]

Thanks 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

[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.

[AndySmith]

Hoping to bump this to see if there may be a solution? 

@amitchandak - do you have any thoughts?

Thanks

[amitchandak]

@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))))