Hello PBi Community,
I have two table visuals. You can see them below (one on the left, one on the right). The one on the left represents products in our forecast. The table on the right indicates items that have actually been ordered.
The two relevant formulas, and the data model, are shown below.
The "Match" formula works as written. But I'm not sure how to add to it to get the part I am missing.
I have 3 potential statuses: Forecasted - Ordered. Unforecasted - Ordered Forecasted - Unordered.
The first formula does a good at getting me either a blank value or a non-blank value in the Match Item column.
Then the 2nd formula takes over and says....if the item showed up on the forecasted table...and also on the order table...then Forecasted - Ordered. If the Match Item column is blank, it means it is showing up as an order (in another column), but it was not on the Forecasted table.
It is the 3rd status that I need help with. Basically I need the formula to say....if the item is on the Forecast table...but does not show up on the Order table....then it is Forecasted - Unordered. I tried adding an additional IF statment below, but could not figure it out. (was also thinking that using VAR statements might work...but again not really sure how to construct it).
Any help is appreciated. The "items" are connected via an inactive relationship as you can see in the data model.
Match Item = CALCULATETABLE(SUMMARIZE(Forecasts,Forecasts[Item]), USERELATIONSHIP(Sales_Orders_for_Charts[Item],Forecasts[Item]))
Match =
IF (
Sales_Orders_for_Charts[Match Item] <> BLANK(),
"Forecasted - Ordered",
IF (
Sales_Orders_for_Charts[Match Item]
= BLANK(),
"Unforecasted-Ordered",
"Forecasted-Unordered")
)
