Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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[Month_Num] <= MAX(Actual[Month_Num])))
Measure 2
Forecast_Var = CALCULATE(SUM(Forecast[Value]), FILTER(Forecast, Forecast[Month_Num] >= MONTH(TODAY())))
I did it in Excel and this is the result I wanted to appear in power bi
May + June =
4228 + 315,489 = 319,717
June + July
319,717 + 132,467,489 = 132,782,978
and so on ......
Here is the dummy file
I really appreciate your help
Solved! Go to Solution.
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 )
@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. 🙂
Hi @Stuznet
This will need to create a new table with some measures and columns.
Where do you apply filters?
Which is static and which is dynamic?
Because my solution may be static for some part.
Best Regards
Maggie
I'm unsure what you mean but this is the third measure I created but it doesn't sum May + June, June + July.
Total = SWITCH(TRUE(), MAX(Category[Category]) = "Cat1", [Forecast_Var] + [Var])
It took me a bit, but I think what you want is a cumulative sum? Your first measure is a cumulative sum, but then you get weird in the forecasting bit.
If you could explain how you calculate the Forecast[Value] in the first place, it would be very helpful. In your expected outcome, it looks like Measure2 is already set up as a reverse cumulative sum (essentially summing everything that hasn't happened yet), and then you're looking to add the previous month's reverse sum again in the table?
If this is truly the case, you may be able to use this measure to get the forecasted month values:
Forecast_Var_plus_prev_month = CALCULATE(SUM(Forecast[Value]), FILTER(Forecast, Forecast[Month_Num] >= MONTH(TODAY())-1))
Either way, the math that I'm seeing seems weird, and I'd love to know what's up.
I apologize I couldn't better explain to you. The function you provided isn't what I'm looking for.
I just sum the forecast value,
CALCULATE(SUM(Forecast[Value]),
with this formula, I want it to start summing from June and not May.
FILTER(Forecast, Forecast[Month_Num] >= MONTH(TODAY())))
I did this in Excel, I just want to illustrate.
I hope that make sense
Ah, then you probably want an IF or SWITCH statement:
Assuming you have a list of months already pre-generated, here's some pseudocode that should get you closer:
CalcColumn =
VAR PrevValue = CALCULATE(ThisTable[Value], FILTER(ALL(ThisTable), ThisTable[Month] < EARLIER(ThisTable[Month]) && ThisTable[Month] = MAX(ThisTable[Month])) )
RETURN
IF( ThisTable[Month]<MONTH(NOW()), [Measure1], [Measure2] + PrevValue)
This should get you at least closer. Getting the previous value in a row via DAX is tricky at best.
I've tried your formula, but the result still the same. It didn't sum....here is the dummy file.
CalcColumn2 = VAR PrevValue = CALCULATE(Forecast[Forecast_Var], FILTER(ALL(Forecast), Forecast[Month_Num] < EARLIER(Forecast[Month_Num]) && Forecast[Month_Num] = MAX(Forecast[Month_Num]))) RETURN IF(Forecast[Month_Num] < MONTH(NOW()), [Var], [Forecast_Var] + PrevValue)
Am doing something wrong?
Hi,
Is this the result you are expecting? You may download my PBI file from here.
Hope this helps.
Unfortunately, it is not the result I'm looking for. I appreciated your help
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 )
@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. 🙂
Do you have a calendar table?
if so, first create a measure which delivers the actual value for the previous month. Something along the lines of:
previous month actual = CALCULATE([Actual],
FILTER(ALL(CalendarTable),
CalendarTable[Month] = MAX(CalendarTable[Month]) -1))
then create new measure summing the current month forecast with this previous month actual. Use the calendarMonth as your x-axis.
if you want the cumulative, create the cumulative measure with this measure as you did with your forecast measure.
(apologies if it needs polishing but I’m not in front of a pc at the moment)
EDIT: here is a pic of the proposed solution
Proud to be a Super User!
Paul on Linkedin.
User | Count |
---|---|
92 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |