Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
Solved! Go to 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.
Thank you!
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.
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
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
146 | |
75 | |
64 | |
52 | |
47 |
User | Count |
---|---|
218 | |
87 | |
71 | |
63 | |
60 |