topic Re: Divide doesn't work with measure that filters on date in DAX Commands and Tips

Divide doesn't work with measure that filters on date

alarekawek — Fri, 28 Mar 2025 17:25:24 GMT

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?

Re: Divide doesn't work with measure that filters on date

bhanu_gautam — Sat, 29 Mar 2025 04:25:25 GMT

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

Re: Divide doesn't work with measure that filters on date

alarekawek — Sat, 29 Mar 2025 13:40:01 GMT

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

Re: Divide doesn't work with measure that filters on date

alarekawek — Sat, 29 Mar 2025 14:03:03 GMT

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

Re: Divide doesn't work with measure that filters on date

techies — Sun, 30 Mar 2025 06:51:28 GMT

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

)

Re: Divide doesn't work with measure that filters on date

alarekawek — Sun, 30 Mar 2025 13:26:39 GMT

Thank you it works!!