Power BI Matrix

[CasperSV]

Hi guys,

I have made a Matrix in Power BI, however it should exclude the type Forecast as soon as the type Sales order (named order in report) has the same amount. 

Current result:

Do you guys know how to edit the measure to get to this result?

Thanks in advance!

Download report:

Test Power BI Demand forecast.pbix

Best regards,

Casper

[CasperSV]

Thank you!

[Anonymous]

Hi @CasperSV ,

Please try below dax formula and add it to matrix value:

Available per week (Switch) =
VAR _a =
    CALCULATE (
        [Mutations this week],
        FILTER ( ALL ( Lines ), [Type] = "Forecast" )
    )
VAR _b =
    CALCULATE ( [Mutations this week], FILTER ( ALL ( Lines ), [Type] = "Order" ) )
VAR _c =
    IF (
        MAX ( 'Lines'[Type] ) = "Forecast"
            && _a = _b,
        BLANK (),
        [Mutations this week]
    )
RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( 'Lines'[Type] ), _c,
        NOT ISINSCOPE ( 'Lines'[Type] ), [Available quantity per week]
    )

Please refer the attached .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CasperSV]

Hi @Anonymous 

Thanks a lot for your effort and help!

It is almost the solution I need, however the Total in week 47 is still -8.

It should be 0, because the order is replacing the forecast: Inventory (8) - Order (8) = 0.

Best regards,

Casper

[Anonymous · Accepted solution]

Hi @CasperSV ,

Please replace before measure with below dax formula:

Available per week (Switch) =
VAR _a =
    CALCULATE (
        [Mutations this week],
        FILTER ( ALL ( Lines ), [Type] = "Forecast" )
    )
VAR _b =
    CALCULATE ( [Mutations this week], FILTER ( ALL ( Lines ), [Type] = "Order" ) )
VAR _c =
    IF (
        MAX ( 'Lines'[Type] ) = "Forecast"
            && _a = _b,
        BLANK (),
        [Mutations this week]
    )
RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( 'Lines'[Type] ), _c,
        ISINSCOPE ( 'Lines'[Item number] ),
            IF (
                _a = _b,
                [Available quantity per week] - _a,
                [Available quantity per week]
            ),
        BLANK ()
    )

Please refer the attached .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.