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. The issue I'm faced with is coming up with a calculation which uses 3 other calculations that are sliced on [Months Since Reg] (on columns) however the final result will be a single value.
To explain further;
Pivot [1] is my base [% Split] calculation respective to the [Months Since Reg]
Pivot [2] shows the [Value Per Player] over the [Months Since Reg]
Pivot [3] shows the multiplcation on [% Split] * [Value Per Player] with respct to the [Months Since Reg] to get [Adj Value Per Player]
Then finally I want to calculate my [Expected Value] in green which is the sum of all [Adj Value Per Player]
So, as you can see it's a multi-step process where I'd need to utilise several table variables to reach my result, however my DAX skills don't extend to such expertise.
Can anyone shed some light on how I may achieve this?
Thanks.
Solved! Go to Solution.
[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.
Can 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] )
@Anonymous
fair point
Hey 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
[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.
That did the trick @Anonymous . Much appreciated.
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 |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |