Forum Discussion

Radhika2605's avatar
Radhika2605
Helper II
5 years ago
Solved

Need help with a measure

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.

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
    Radhika2605
    Helper II
    5 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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.

  • Radhika2605's avatar
    Radhika2605
    Helper II
    5 years ago

    Hi Rico,

     

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

     

    Radhika