topic Re: measure is returing wrong total value in table visual in DAX Commands and Tips

measure is returing wrong total value in table visual

lawadaa — Mon, 08 May 2023 11:06:18 GMT

hi everyone

i have created a measure that calculates the sales agents incentive. it's returning value per row correct but the total value in the table visual is wrong

the measure is :

new agent incentive = CALCULATE([new commissin sum]*max('Agent Rate Key'[Agent Rate]))

new commission sum measure :

new commissin sum = Sumx(Summarize('categories trans','categories trans'[categories summarize],'categories trans'[base type without zeed], "_1", [new commission calculation]), [_1])

new commission calculation measure :

new commission calculation = IF([new achiev %]<= 0.8999,0, IF(([new achiev %]>=.90 &&[new achiev %]<=.9499),300-(.1*300), IF([new achiev %]>=.95 &&[new achiev %]<=.9999,300-(.05*300), IF([new achiev %]>=1 &&[new achiev %]<=1.09,300, IF([new achiev %]>= 1.1 &&[new achiev %]<=1.1999,300+(.1*300), IF([new achiev %]>=1.2 &&[new achiev %]<=1.29999,300+(.2*300),IF([new achiev %]>=1.3,300+(.3*300),0)))))))

Re: measure is returing wrong total value in table visual

tamerj1 — Mon, 08 May 2023 11:25:10 GMT

Hi lawadaa
Please try

new agent incentive = SUMX ( SUMMARIZE ( 'Agent Rate Key', 'Agent Rate Key'[T_NAME], 'Agent Rate Key'[categories summarize], 'Agent Rate Key'[base type without zeed] ), [new commissin sum] * CALCULATE ( MAX ( 'Agent Rate Key'[Agent Rate] ) ) )

Re: measure is returing wrong total value in table visual

lawadaa — Mon, 08 May 2023 12:30:22 GMT

it's still returing the same total198. correct total should be 112.5

Re: measure is returing wrong total value in table visual

tamerj1 — Mon, 08 May 2023 12:35:57 GMT

lawadaa
Would you please paste a screenshot of the code that you have used?

Re: measure is returing wrong total value in table visual

lawadaa — Mon, 08 May 2023 12:49:05 GMT

Re: measure is returing wrong total value in table visual

tamerj1 — Mon, 08 May 2023 18:06:06 GMT

lawadaa

Please try

new agent incentive =
SUMX (
SUMMARIZE (
'Agent Rate Key',
'Agent Rate Key'[T_NAME],
'Agent Rate Key'[categories summarize],
'Agent Rate Key'[base type without zeed],
'Agent Rate Key'[Agent Rate]
),
[new commissin calculation] * 'Agent Rate Key'[Agent Rate]
)