Forum Discussion

CasperSV's avatar
CasperSV
Helper II
3 years ago
Solved

Power BI Matrix

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

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

     

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      CasperSV
      Helper II
      3 years ago

      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's avatar
        Anonymous
        Not applicable
        3 years ago

        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.