Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
alarekawek
Frequent Visitor

Divide doesn't work with measure that filters on date

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

SegmentYearScenarioLocationTypeMonthValueDateMonth No
America20251+11ChileGMJan100.001-Jan-251
America20251+11ChileGMFeb150.001-Feb-252
America20251+11ChileGMMar170.001-Mar-253
America20251+11ChileMarginJan2.001-Jan-251
America20251+11ChileMarginFeb1.881-Feb-252
America20251+11ChileMarginMar2.831-Mar-253
America20251+11ChileVolumeJan50.001-Jan-251
America20251+11ChileVolumeFeb80.001-Feb-252
America20251+11ChileVolumeMar60.001-Mar-253
America20252+10ChileGMJan100.001-Jan-251
America20252+10ChileGMFeb120.001-Feb-252
America20252+10ChileGMMar170.001-Mar-253
America20252+10ChileMarginJan2.001-Jan-251
America20252+10ChileMarginFeb2.401-Feb-252
America20252+10ChileMarginMar2.831-Mar-253
America20252+10ChileVolumeJan50.001-Jan-251
America20252+10ChileVolumeFeb50.001-Feb-252
America20252+10ChileVolumeMar60.001-Mar-253

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 2025CALCULATE(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?

1 ACCEPTED SOLUTION
techies
Super User
Super User

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  
        )
    )
 
a.png
― Power BI | Microsoft Fabric | PL-300 | DP-600 | Blog: medium.com/@cseprs_54978

View solution in original post

5 REPLIES 5
techies
Super User
Super User

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  
        )
    )
 
a.png
― Power BI | Microsoft Fabric | PL-300 | DP-600 | Blog: medium.com/@cseprs_54978

Thank you it works!! 

bhanu_gautam
Super User
Super User

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




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






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

alarekawek_0-1743255590183.png

 

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

alarekawek_1-1743256957356.png

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.