Divide doesn't work with measure that filters on date

[alarekawek]

Hi I've got regional results data that is showing Gross margin, volumes and margin values for each forecasting scenario (1+11 meaning we've got 1 month of actuals and 11 months forecasted, 2+10 meaning 2 months actual and 10 forecasted etc) for 2025 in a following structure

Segment	Year	Scenario	Location	Type	Month	Value	Date	Month No
America	2025	1+11	Chile	GM	Jan	100.00	1-Jan-25	1
America	2025	1+11	Chile	GM	Feb	150.00	1-Feb-25	2
America	2025	1+11	Chile	GM	Mar	170.00	1-Mar-25	3
America	2025	1+11	Chile	Margin	Jan	2.00	1-Jan-25	1
America	2025	1+11	Chile	Margin	Feb	1.88	1-Feb-25	2
America	2025	1+11	Chile	Margin	Mar	2.83	1-Mar-25	3
America	2025	1+11	Chile	Volume	Jan	50.00	1-Jan-25	1
America	2025	1+11	Chile	Volume	Feb	80.00	1-Feb-25	2
America	2025	1+11	Chile	Volume	Mar	60.00	1-Mar-25	3
America	2025	2+10	Chile	GM	Jan	100.00	1-Jan-25	1
America	2025	2+10	Chile	GM	Feb	120.00	1-Feb-25	2
America	2025	2+10	Chile	GM	Mar	170.00	1-Mar-25	3
America	2025	2+10	Chile	Margin	Jan	2.00	1-Jan-25	1
America	2025	2+10	Chile	Margin	Feb	2.40	1-Feb-25	2
America	2025	2+10	Chile	Margin	Mar	2.83	1-Mar-25	3
America	2025	2+10	Chile	Volume	Jan	50.00	1-Jan-25	1
America	2025	2+10	Chile	Volume	Feb	50.00	1-Feb-25	2
America	2025	2+10	Chile	Volume	Mar	60.00	1-Mar-25	3

I want to create a matrix table that shows Segment > Location > Type in rows and in columns whole 2025 and actuals based on scenario slicer, - so if I select 2+10 in slicer it will show me only values from this scenario - for total year and for 2 months. 

In order to do that I've created separate measures for each column - for Year Total and for Actuals:

• Total 2025 = CALCULATE(SUM(Append1[Value]), Append1[Year] = "2025", Append1[Scenario]=SELECTEDVALUE(Sort[Scenario])

• Actuals 2025 = CALCULATE(SUM(Append1[Value]), FILTER(Append1, Append1[Month No] <= [Switch]), Append1[Year] = "2025", Append1[Scenario]=SELECTEDVALUE(Sort[Scenario]))

Switch returns number of actual months for each scenario (for 1+11, it's 1, for 2+10 it's 2 etc). 

It works perfectly well for Gross Margin and Volumes, but for margin it obviously sums margin value from 2 months (when 2+10 scenario is selected) 

To avoid this I created following formula, which checks, whether we're in Margin line and if so is supposed to return the Actuals margin calculated as a Actuals Gross Margin divided by Actuals Volumes

• Actuals 2025 Corrected = IF(SELECTEDVALUE(Append1[Type])="Margin",
  DIVIDE(CALCULATE(SUM(Append1[Value]), FILTER(Append1, Append1[Month No] <= [Switch]), Append1[Year] = "2025", Append1[Scenario]=SELECTEDVALUE(Sort[Scenario]), Append1[Type]="GM"),CALCULATE(SUM(Append1[Value]), FILTER(Append1, Append1[Month No] <= [Switch]), Append1[Year] = "2025", Append1[Scenario]=SELECTEDVALUE(Sort[Scenario]), Append1[Type]="Volume"),0), 
  CALCULATE(SUM(Append1[Value]), FILTER(Append1, Append1[Month No] <= [Switch]), Append1[Year] = "2025", Append1[Scenario]=SELECTEDVALUE(Sort[Scenario])))

The formula unfortunately returns blanks in Margin line, but it works perfectly when calculating 2025 totals - when I don't have to apply additional filter to get only <= month based on scenario. 

• Total 2025 = IF(SELECTEDVALUE(Append1[Type])="Margin",
  DIVIDE(CALCULATE(SUM(Append1[Value]), Append1[Year] = "2025", Append1[Scenario]=SELECTEDVALUE(Sort[Scenario]), Append1[Type]="GM"),CALCULATE(SUM(Append1[Value]), Append1[Year] = "2025", Append1[Scenario]=SELECTEDVALUE(Sort[Scenario]), Append1[Type]="Volume"),0), 
  CALCULATE(SUM(Append1[Value]), Append1[Year] = "2025", Append1[Scenario]=SELECTEDVALUE(Sort[Scenario])))

Can you please help me with how should I build the divide part for actuals so that is works?

[techies · Accepted solution]

Hi @alarekawek please try this

Actuals 2025 Corrected =

VAR SelectedSwitch = SELECTEDVALUE(Sort[Switch], 0)  

VAR Scenario_Number = VALUE(LEFT(SELECTEDVALUE(Sort[Scenario]), FIND("+", SELECTEDVALUE(Sort[Scenario])) - 1))

VAR GM_Value =

    CALCULATE(

        SUM(Append1[Value]),

        Append1[Type] = "GM",

        Append1[yearnn] = 2025,

        Append1[Scenario] = SELECTEDVALUE(Sort[Scenario]),

        Append1[Monthn] <= Scenario_Number  

    )

VAR Volume_Value =

    CALCULATE(

        SUM(Append1[Value]),

        Append1[Type] = "Volume",

        Append1[yearnn] = 2025,

        Append1[Scenario] = SELECTEDVALUE(Sort[Scenario]),

        Append1[Monthn] <= Scenario_Number  

    )

RETURN

    IF(

        SELECTEDVALUE(Append1[Type]) = "Margin",

        IF(

            NOT(ISBLANK(GM_Value)) && NOT(ISBLANK(Volume_Value)),

            DIVIDE(GM_Value, Volume_Value, 0),

            BLANK()

        ),

        CALCULATE(

            SUM(Append1[Value]),

            Append1[yearnn] = 2025,

            Append1[Scenario] = SELECTEDVALUE(Sort[Scenario]),

            Append1[Monthn] <= Scenario_Number  

        )

    )

[alarekawek]

Thank you it works!! 

[bhanu_gautam]

@alarekawek , Try using

Actuals 2025 Corrected =
IF(
SELECTEDVALUE(Append1[Type]) = "Margin",
DIVIDE(
CALCULATE(
SUM(Append1[Value]),
FILTER(
Append1,
Append1[Month No] <= [Switch] &&
Append1[Year] = "2025" &&
Append1[Scenario] = SELECTEDVALUE(Sort[Scenario]) &&
Append1[Type] = "GM"
)
),
CALCULATE(
SUM(Append1[Value]),
FILTER(
Append1,
Append1[Month No] <= [Switch] &&
Append1[Year] = "2025" &&
Append1[Scenario] = SELECTEDVALUE(Sort[Scenario]) &&
Append1[Type] = "Volume"
)
),
0
),
CALCULATE(
SUM(Append1[Value]),
FILTER(
Append1,
Append1[Month No] <= [Switch] &&
Append1[Year] = "2025" &&
Append1[Scenario] = SELECTEDVALUE(Sort[Scenario])
)
)
)

[alarekawek]

Hi, thank you for the answer, unfortunately this does not solve the problem 😞 

[alarekawek]

I dividided the margin calculation formula into parts and it does work, but I just cannot get it to show in the margin line, it shows only in the Chile level