Forum Discussion

Quite_lost's avatar
Quite_lost
Frequent Visitor
5 years ago
Solved

Measure returning unexpected value

Hi, I quite new to PowerBI and DAX structure and need some help.

I am trying to make an Inventory History dashboard that has a measure Average Sales by month in the context of shortage of stock of the product. The period when there is a shortage of "Product A" must not be considered in the average, thus "Average SAles by Month "in stock".

 

The problem is: the expected result differs from what is shown. For an example, in the period between 01/10/2019 to 30/09/2020 (dd/mm/yyyy) the expect average would be 18930, not 17633.

 

 

Also, how do I upload the dummy file in here?

 

Any help will be much appreciated.

 

Best wishes.

Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    5 years ago

    Hi Quite_lost ,

     

    Based on your description, I checked your example pbix and found the problem.

    The reason is that you turn off the interaction between Movement Type slicer and Days "in stock".

    Correspondingly, you need to clear the filter of Movement Type in the calculation.

    Average Sales by Month "in stock" = var t = [Total Quantity]
var m = CALCULATE([Months "in stock"],ALL('Stock History'[Movement Type]))
return DIVIDE(t,m)

     

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

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Quite_lost , can you share formulas of three involved

     

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

    • Quite_lost's avatar
      Quite_lost
      Frequent Visitor
      5 years ago

      Here is the pbix file.

       

      Here are the formulas I'm using:

       

      #Total Quantity = SUM('Stock History'[Quantity]) *-1

#Cumulative Total = CALCULATE( [Total Quantity], FILTER( ALL( Dates[Date] ), Dates[Date] <= MAX( Dates[Date] ) ) )

#Days "in stock" = COUNTROWS(FILTER(ADDCOLUMNS(CROSSJOIN( VALUES( Dates[Date] ), VALUES('Stock History'[Product]) ), "Inventory Levels", [Cumulative Total] ), [Inventory Levels] <> 0 && NOT( ISBLANK( [Inventory Levels] ) ) ) )-1

#Days "zero stock" = COUNTROWS( FILTER( ADDCOLUMNS( CROSSJOIN( VALUES( Dates[Date] ), VALUES('Stock History'[Product]) ), "Inventory Levels", [Cumulative Total] ), [Inventory Levels] = 0 && NOT( ISBLANK( [Inventory Levels] ) ) )

#Months "in stock" = [Days "in stock"] / 30.41666

#Average Sales by Month "in stock" = [Total Quantity] / [Months "in stock"]

       

       

      • V-lianl-msft's avatar
        V-lianl-msft
        Community Support
        5 years ago

        Hi Quite_lost ,

         

        Based on your description, I checked your example pbix and found the problem.

        The reason is that you turn off the interaction between Movement Type slicer and Days "in stock".

        Correspondingly, you need to clear the filter of Movement Type in the calculation.

        Average Sales by Month "in stock" = var t = [Total Quantity]
var m = CALCULATE([Months "in stock"],ALL('Stock History'[Movement Type]))
return DIVIDE(t,m)

         

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