cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## 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?

Test Power BI Demand forecast.pbix

Best regards,

Casper

1 ACCEPTED SOLUTION
Community Support

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 4
Helper II

Thank you!

Community Support

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.

Helper II

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

Community Support

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.