Forum Discussion

djbuncle's avatar
djbuncle
Icon for Helper I rankHelper I
7 years ago
Solved

Multi-Step Calculation

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 e...
DAX Query
SUMMARIZE
  • Anonymous's avatar
    Anonymous
    7 years ago
    [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.

Stachu's avatar
Stachu
Icon for Community Champion rankCommunity Champion
7 years ago

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).

Column1Column2
A1
B2.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] )
djbuncle's avatar
djbuncle
Icon for Helper I rankHelper I
7 years ago

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.

 

CountryMonthsSinceRegSplitValuePerPlayer
Germany0                   1.00145
Germany1                   0.14317
Germany2                   0.09303
Germany3                   0.07248
Germany4                   0.06265
Germany5                   0.05239
Germany6                   0.04274
Germany7                   0.04242
Germany8                   0.04227
Germany9                   0.03203
Germany10                   0.03202
Germany11                   0.03262
Germany12                   0.02404
Germany13                   0.02363
Germany14                   0.02352
Germany15                   0.02248
Germany16                   0.02219
Germany17                   0.01364
Germany18                   0.01467
Germany19                   0.011,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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago
    [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's avatar
      djbuncle
      Icon for Helper I rankHelper I
      7 years ago

      That did the trick Anonymous . Much appreciated.