Question

I am trying to create a measure in POWER BI, its to calculate the total revenue for a time period but it has a lot of conditions to it. i.e.

Total Revenue with following conditions Shipping type = "Incoming"

Status = Invoiced,Shipped,Received

if site = 29104 exclude product = S-ExtFreight,S-ProductInDelivery,S-WeighOnly

if site = 33103 exclude product = S-ExtFreight,S-WeighOnly

if site = 23103 exclude product = S-ExtFreight,SAxleWeigh

I tried to use the below expression but its not throwing me any values when I plot it on a graph.

Measure 2 = CALCULATE( sum( Fact table [Amount]), Fact table [Site Code] = 29104 & Fact table [Item Number] in {"S-ExtFreight","S-ProductInDelivery","S-WeighOnly"}, Fact table [Site Code] = 33103 & Fact table [Item Number] in {"S-ExtFreight","S-WeighOnly"}, Fact table [Site Code] = 23103 & Fact table [Item Number] in {"S-ExtFreight","S-AxleWeigh"})

Answer 1

You have to use NOT in to exclude products. Also you need to use && for AND condition.

Measure 2 = CALCULATE( sum( Fact table [Amount]),  Fact table [Site Code] = 29104 &&  Fact table [Item Number] NOT in {"S-ExtFreight","S-ProductInDelivery","S-WeighOnly"},  Fact table [Site Code] = 33103 &&  Fact table [Item Number] NOT in {"S-ExtFreight","S-WeighOnly"},  Fact table [Site Code] = 23103 &&  Fact table [Item Number] NOT in {"S-ExtFreight","S-AxleWeigh"})

友情链接