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 t...
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.

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.

      • Quite_lost's avatar
        Quite_lost
        Frequent Visitor
        5 years ago

        Wow, that really worked!

        Thank you very much!

        I need to learn a lot more about filter and contexts.