Forum Discussion
Measure not displaying correct total value
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?
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.
5 Replies
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.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)