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

Hi Hussein_charif 

A separate date dimensions table with a complete set of dates make time intelligence calculations easier.

You can see in the screenshot below that even when the year changes, the calculations from the previous periods are still being carried over to the current.

 

Sample calculations are in the attached pbix.

  • 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?