Measure not displaying correct total value

[Anonymous]

Hi all,

I created this measure:

BonusTestM = 
VAR BonusMin = SELECTEDVALUE('BonusValue'[BonusMin])
VAR BonusMax = SELECTEDVALUE('BonusValue'[BonusMax])
VAR BonusValueMin = SELECTEDVALUE('BonusValue'[BonusValueMin])
VAR BonusValueMax = SELECTEDVALUE('BonusValue'[BonusValueMax])
RETURN
SWITCH(
TRUE()
,SUM(Income[Value]) < BonusMin, 0
,SUM(Income[Value]) < BonusMax, BonusValueMin
,BonusValueMax
)

These are the tables:

It basicly calculates bonus values based on the conditions, the output is this:

I tried to use SUMX and then it outputs this:

BonusTestM1 = 
VAR BonusMin = SELECTEDVALUE('BonusValue'[BonusMin])
VAR BonusMax = SELECTEDVALUE('BonusValue'[BonusMax])
VAR BonusValueMin = SELECTEDVALUE('BonusValue'[BonusValueMin])
VAR BonusValueMax = SELECTEDVALUE('BonusValue'[BonusValueMax])
RETURN
SUMX(
  VALUES(Income[Value])
,
SWITCH(
TRUE()
,SUM(Income[Value]) < BonusMin, 0
,SUM(Income[Value]) < BonusMax, BonusValueMin
,BonusValueMax
)
)

I noticed this topic: https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/td-p/547907

But I'm not sure how I should use this in my case. 

Can anyone help me with getting the correct total values?

[v-jingzhang · Accepted solution]

Hi @Anonymous 

You can use two measures to achieve it. Put the first single measure in the second total measure, just like below:

BonusTestM = 
VAR BonusMin = SELECTEDVALUE('BonusValue'[BonusMin])
VAR BonusMax = SELECTEDVALUE('BonusValue'[BonusMax])
VAR BonusValueMin = SELECTEDVALUE('BonusValue'[BonusValueMin])
VAR BonusValueMax = SELECTEDVALUE('BonusValue'[BonusValueMax])
VAR SumOfValue = SUM(Income[Value])
RETURN
SWITCH(
TRUE(),
SumOfValue < BonusMin, 0,
SumOfValue < BonusMax, BonusValueMin,
BonusValueMax
)

BonusTestTotal = SUMX(VALUES(Income[Value]),[BonusTestM])

Measures are calculated based on the context they are in. Other columns you put in the table visual will influence the context of measures, along with the filters outside.

In this case, with VALUES(Income[Value]) in the total measure, it will work when all the Values are distinct. If there are duplicate Income[Value], it may not work because VALUES() always returns the distinct values from a column.

Kindly let me know if this helps.
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

[Anonymous]

Thanks a lot! And thanks for the explanation. I'll implement it this way. 

[Anonymous]

if you create your measure as a column instead, it would total correctly

[Anonymous]

BonusTestM_col =
VAR BonusMin = related(BonusValue[BonusMin])
VAR BonusMax = related(BonusValue[BonusMax])
VAR BonusValueMin = related(BonusValue[BonusValueMin])
VAR BonusValueMax = related(BonusValue[BonusValueMax])

RETURN
SWITCH(
TRUE()
,[Value] < BonusMin, 0
,[Value] < BonusMax, BonusValueMin
,BonusValueMax)

[Anonymous]

Thanks, this does work, but I'd actually rather use a measure. Can you explain why a column works for total value and a measure doesn't? I'm interested in this issue..