Forum Discussion
Stuznet
7 years agoHelper V
How to Sum two values into one Row
Hi guys, I'm struggling to sum two measures into one Row. I have two Measure Measure 1 Var = CALCULATE(SUM([Value]),
FILTER(
ALLSELECTED(Actual),
Actual[Mont...
- Anonymous7 years ago
Try this measure
Result = VAR LastMonthInActuals = MAX ( Actual[Month_Num] ) VAR MonthInContext = MAX ( Month_Tbl[Month_Num] ) VAR CumulativeTotalOfActual = SUMX ( ALLSELECTED ( Actual ), Actual[Value] ) VAR CumulativeMonthlyActuals = CALCULATE ( SUM ( [Value] ), FILTER ( ALLSELECTED ( Actual ), Actual[Month_Num] <= LastMonthInActuals ) ) VAR CumulativeForecast = SUMX ( FILTER ( ALL ( Forecast ), Forecast[Month_Num] > [LastMonthInActuals] && Forecast[Month_Num] <= MonthInContext ), Forecast[Value] ) RETURN IF ( MonthInContext <= LastMonthInActuals, CumulativeMonthlyActuals, CumulativeForecast + CumulativeTotalOfActual ) - 7 years ago
Anonymous
Thank you so much for the measure, the result was wrong but I modified it and it works :)
See measure in Red. Your original measure was Forecast[Value], I replaced with Forecast[Forecast_Var])
Forecast_Var = CALCULATE(SUM(Forecast[Value]), FILTER(Forecast, Forecast[Month_Num] >= MONTH(TODAY())))This is the final measure
Measure = VAR LastMonthInActuals = MAX ( Actual[Month_Num] ) VAR MonthInContext = MAX ( Month_Tbl[Month_Num] ) VAR CumulativeTotalOfActual = SUMX ( ALLSELECTED ( Actual ), Actual[Value] ) VAR CumulativeMonthlyActuals = CALCULATE (SUM ( Actual[Value] ),FILTER ( ALLSELECTED ( Actual ), Actual[Month_Num] <= LastMonthInActuals )) VAR CumulativeForecast = SUMX (FILTER (ALL ( Forecast ),Forecast[Month_Num] > LastMonthInActuals && Forecast[Month_Num] <= MonthInContext), Forecast[Forecast_Var]) RETURN IF ( MonthInContext <= LastMonthInActuals, CumulativeMonthlyActuals, CumulativeForecast + CumulativeTotalOfActual)This the result and it summing correctly.
Thank you so much y'all. :)
Anonymous
7 years agoNot applicable
Try this measure
Result =
VAR LastMonthInActuals =
MAX ( Actual[Month_Num] )
VAR MonthInContext =
MAX ( Month_Tbl[Month_Num] )
VAR CumulativeTotalOfActual =
SUMX ( ALLSELECTED ( Actual ), Actual[Value] )
VAR CumulativeMonthlyActuals =
CALCULATE (
SUM ( [Value] ),
FILTER ( ALLSELECTED ( Actual ), Actual[Month_Num] <= LastMonthInActuals )
)
VAR CumulativeForecast =
SUMX (
FILTER (
ALL ( Forecast ),
Forecast[Month_Num] > [LastMonthInActuals]
&& Forecast[Month_Num] <= MonthInContext
),
Forecast[Value]
)
RETURN
IF (
MonthInContext <= LastMonthInActuals,
CumulativeMonthlyActuals,
CumulativeForecast + CumulativeTotalOfActual
)Stuznet
7 years agoHelper V
Anonymous
Thank you so much for the measure, the result was wrong but I modified it and it works :)
See measure in Red. Your original measure was Forecast[Value], I replaced with Forecast[Forecast_Var])
Forecast_Var = CALCULATE(SUM(Forecast[Value]),
FILTER(Forecast, Forecast[Month_Num] >= MONTH(TODAY())))
This is the final measure
Measure =
VAR LastMonthInActuals = MAX ( Actual[Month_Num] )
VAR MonthInContext = MAX ( Month_Tbl[Month_Num] )
VAR CumulativeTotalOfActual = SUMX ( ALLSELECTED ( Actual ), Actual[Value] )
VAR CumulativeMonthlyActuals = CALCULATE (SUM ( Actual[Value] ),FILTER ( ALLSELECTED ( Actual ), Actual[Month_Num] <= LastMonthInActuals ))
VAR CumulativeForecast = SUMX (FILTER (ALL ( Forecast ),Forecast[Month_Num] > LastMonthInActuals && Forecast[Month_Num] <= MonthInContext), Forecast[Forecast_Var])
RETURN
IF (
MonthInContext <= LastMonthInActuals,
CumulativeMonthlyActuals,
CumulativeForecast + CumulativeTotalOfActual)
This the result and it summing correctly.
Thank you so much y'all. :)