Forum Discussion
Helper IMulti-Step Calculation
[Expected Value] = SUMX( ALL ( T[Months Since Reg ] ), [% Split] * [Value Per Player] )
You can try the above... but I think you'll need some adjustments with respect to other filters. I don't have enough information to tell you exactly what the formula should look like.
By the way, [Months Since Reg] must be an attribute in a dimension (not in a fact table).
Best
D.
Community ChampionCan you add sample tables (in format that can be copied to PowerBI) from your model with anonymised data? Like this (just copy and paste into the post window).
| Column1 | Column2 |
| A | 1 |
| B | 2.5 |
probably it will be something like this
Measure =
VAR __Summary =
SUMMARIZECOLUMNS (
'Table',
'Table'[Country],
'Table'[Months Since Reg],
"Split", [% Split],
"VPP", [Value Per Player]
)
RETURN
SUMX ( __Summary, [Split] * [VPP] )
djbuncleHey Stachu, thanks for your response - I've only got around to giving it a go now.
The added complexity here is that the 2 values to multiply are actual calculations themselves using other tables so I can't really supply some example data easily, but here's what the calculated values would be.
Country MonthsSinceReg Split ValuePerPlayer Germany 0 1.00 145 Germany 1 0.14 317 Germany 2 0.09 303 Germany 3 0.07 248 Germany 4 0.06 265 Germany 5 0.05 239 Germany 6 0.04 274 Germany 7 0.04 242 Germany 8 0.04 227 Germany 9 0.03 203 Germany 10 0.03 202 Germany 11 0.03 262 Germany 12 0.02 404 Germany 13 0.02 363 Germany 14 0.02 352 Germany 15 0.02 248 Germany 16 0.02 219 Germany 17 0.01 364 Germany 18 0.01 467 Germany 19 0.01 1,038 I ended up using SUMMARIZE instead of SUMMARIZECOLUMNS to get the calculation to work, but now when I view this in Excel it only seems to do the calculation in month 0. Note that when I'm pivoting in Excel I don't want to use the [Months Since Reg] attribute, this is just needed in memory to do the required calculation -> the end result is one value for Germany.
Thanks
- djbuncle, please do not use SUMMARIZE for calculating with measures. You should follow the ADDCOLUMNS/SUMMARIZE pattern as outlined here:
https://www.sqlbi.com/articles/best-practices-using-summarize-and-addcolumns/
Best
D. [Expected Value] = SUMX( ALL ( T[Months Since Reg ] ), [% Split] * [Value Per Player] )
You can try the above... but I think you'll need some adjustments with respect to other filters. I don't have enough information to tell you exactly what the formula should look like.
By the way, [Months Since Reg] must be an attribute in a dimension (not in a fact table).
Best
D.
- djbuncle
That did the trick Anonymous . Much appreciated.
- Stachu, you should never use SUMMARIZECOLUMNS in a measure. The reason being that SUMMARIZECOLUMNS does not honor filtering context introduced by CALCULATE. This function is purely for QUERIES.
Best
Darek- Stachu
Anonymous
fair point