Bucketing Value with condition

[betty_bui2602]

Hello all, 

Im pretty new here, hope to get help from everyone to solve this case. 

Dummy table: 

Item	Forecast	Ttl open order + Shipment	Ttl Order - Forecast
A	120	175	55
B	220	95	-125
C	60	0	-60
D	0	100	100

Outcome i would like to get:

Order vs forecast		Forecast	Ttl open order + Shipment	Ttl Order - Forecast
Order > forecast		120	175	55
Order < forecast		220	95	-125
Forecast without orders		60	0	-60
Zero forecast		0	100	100
Total		400	370	-30

TIA 🙂 

[v-shex-msft · Accepted solution]

Hi @betty_bui2602,

You can add a calculated column to check these two field values and return tags. Then you can use these fields to create a table visual to achieve your requirement:

Tag =
VAR diff = Table[Forecast] - Table[Ttl open order + Shipment]
RETURN
    SWITCH (
        Table[Forecast],
        diff, "Forecast without orders",
        0, "Zero forecast",
        IF (
            diff = 0,
            "Order = forecast",
            IF ( diff > 0, "Order > forecast", IF ( diff < 0, "Order < forecast" ) )
        )
    )

Regards,

Xiaoxin Sheng