Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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.
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.
Thanks a lot! And thanks for the explanation. I'll implement it this way.
if you create your measure as a column instead, it would total correctly
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)
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..
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |