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] )
Anonymous's avatar
Anonymous
Not applicable
7 years ago
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's avatar
    Stachu
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Anonymous 
    fair point