Forum Discussion

Hussein_charif's avatar
Hussein_charif
Helper V
1 year ago
Solved

Running total average in matrix not working properly

i have a measure to get "Avg cost" using it in my matrix : AVG Cost= VAR MinDate =     CALCULATE(         MIN(ValTB[Date]),         ALL(ValTB)     ) VAR MaxDate =     MAX(ValTB[Date]) ...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Hussein_charif 

     

    Try this:

     

    AVG Cost = 
VAR MinDate = MIN('ValTB'[Date])
VAR MaxDate = MAX('ValTB'[Date])

RETURN
CALCULATE(
    DIVIDE(
    SUM(ValTB[Cost]),
    SUM(ValTB[Quant]),
    0),
    FILTER(
        ALL(ValTB),
        ValTB[Date] >= MinDate &&
        ValTB[Date] <= MaxDate
    )
)

     

     When you choose to check data for September, it ignores previous dates and only considers data for September.

     

     

    If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

     

    Regards,

    Nono Chen

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

danextian's avatar
danextian
Super User
1 year ago

The problem with your measure is that the average calculation is applied to the whole date column only

 FILTER(
            ALL(ValTB[Date]),
            ValTB[Date] >= MinDate &&
            ValTB[Date] <= MaxDate
        )

 Now, you can't be applying that to the whole ValTB table  or you will get the same value for the whole table with respect to the columns in your variables like in the screenshot below

 

  • Hussein_charif's avatar
    Hussein_charif
    Helper V
    1 year ago

    I removed that context when re applying the measure, but it gave me a complete different set of values which are all incorrect

    • danextian's avatar
      danextian
      Super User
      1 year ago

      How did you remove the context? Where is it applied to? Can you please share a sanitzed copy  of your pbix?