Need help with a measure

[Radhika2605]

I am trying to find out if any sale has been lost or not due to out of stock products and have created a measure for it but when I try to visualize this measure in a table then it first lists all the products without the store names and then all the stores and their relavant products come. In this table I have the following columns - Store Id, Store Name, Product Name, Average Units sold, Stock on hand, Inventory lasts for days and Lost sale measures as shown in the screenshot. First screenshot shows the table visual without lost sale measure and it is perfect but as soon as I bring the Lost sale measure then all the products get listed at the top and afterwards each store and their products come as shown in the second screenshot.

I know this is due to context missing from the Lost Sale measure but I am unable to figure out how can I give the context. Can someone please help. 

Measures used -

Average Units Sold Legit = DIVIDE( [Total Units Sold], COUNTROWS( Dates ) )

Inventory Lasts For (Days) = DIVIDE( [Total Stock On Hand], [Average Units Sold Legit] )

Total Stock On Hand = SUM( Inventory[StockOnHand] )

Lost sale? = IF( [Total Stock On Hand] = 0 && [Average Units Sold Legit] > 0, "Sale Lost", "No Loss")

This is how my model looks

Thanks in advance.

[v-rzhou-msft · Accepted solution]

Hi @Radhika2605 

I think this problem should be caused by your data model. Please check whether there are some products out of the "Store ID" list. Your calculate logic is that [Total Stock On Hand] = 0 && [Average Units Sold Legit] > 0 return "Sale Lost", others return  "No Loss".

In Power BI, the blank value will be recognized as 0. So  [Total Stock On Hand] = 0 && [Average Units Sold Legit] = 0 will return "No Loss" like result in your Screenshot. 

Here I will give you two advices.

1. Result is all right, you can remove blank store ids in filter field.

2. Update your measure code.

Lost sale? =
IF (
    [Total Stock On Hand] = BLANK ()
        && [Average Units Sold Legit] = BLANK (),
    BLANK (),
    IF (
        [Total Stock On Hand] = 0
            && [Average Units Sold Legit] > 0,
        "Sale Lost",
        "No Loss"
    )
)

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Radhika2605]

Hi Rico,

Thanks so much for your reply.  I will look into this one. thanks again!

Radhika 

[Radhika2605]

Hi Rico, 

Thanks heaps for your response. You are an absolute genuis - both the solutions you mentioned worked. I have one question though - I was trying to find in which table this store id is blank but was not able to see any such record. Will it be possible for you to point out which table will it be? Thank again in advance.

[v-rzhou-msft · Accepted solution]

Hi @Radhika2605 

Due to I don't know the details about your data model, I could only give you some advices.

I think there should be a table have Product column and Store column. In this table, there shoule be some products with Store ID which is out of the "Store ID" list. Then you relate this table with other tables which have Product column or Store column.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-rzhou-msft · Accepted solution]

Hi @Radhika2605 

I think this problem should be caused by your data model. Please check whether there are some products out of the "Store ID" list. Your calculate logic is that [Total Stock On Hand] = 0 && [Average Units Sold Legit] > 0 return "Sale Lost", others return  "No Loss".

In Power BI, the blank value will be recognized as 0. So  [Total Stock On Hand] = 0 && [Average Units Sold Legit] = 0 will return "No Loss" like result in your Screenshot. 

Here I will give you two advices.

1. Result is all right, you can remove blank store ids in filter field.

2. Update your measure code.

Lost sale? =
IF (
    [Total Stock On Hand] = BLANK ()
        && [Average Units Sold Legit] = BLANK (),
    BLANK (),
    IF (
        [Total Stock On Hand] = 0
            && [Average Units Sold Legit] > 0,
        "Sale Lost",
        "No Loss"
    )
)

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.