Forum Discussion

danifdez's avatar
danifdez
Regular Visitor
4 years ago

Chart offset for cumulative average

Hi everyone,

I am trying to show a cumulative average for my google reviews stars with the following formula:

 

 

Stars = 
CALCULATE(
CALCULATE(SUM(tabla1[Rating]),filter(allselected(tabla1[Date]),tabla1[Date] <= MAX(tabla1[Date]))) / CALCULATE(count(tabla1[Rating]), filter(allselected(tabla1[Date]),tabla1[Date] <= MAX(tabla1[Date]))))

 

 

Problem is that the first values are too extreme and the chart looks kind of biased. I tried filter by date but the result is the same but starting to sum the average from a further date. 

 


Is there any option to just filter/slice the chart without affecting the data?

Thanks in advance

Need Help

3 Replies

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    4 years ago

    Hi danifdez 

    >>Is there any option to just filter/slice the chart without affecting the data?

    What is the expected outcome? Could you please share some sample data and its expected result? Thanks.

     

     

    Best Regards,

    Community Support Team _Tang

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

  • danifdez's avatar
    danifdez
    Regular Visitor
    4 years ago

    Hi v-xiaotang,

    the outcome I have in mind is somthing like this:

     

    Basically I still want the numers to keep averaging themselves cumulatively but I dont want the noise from the begining. The problem is mainly the first 4 values of five stars: (5+5+5+5)/4 creates a super extreme value.

    I dont really want to change the input only the way its represented. 
    I tried filtering by date but the problem is more or less the same as it starts averaging but from a further point in time.

    Any idea?

    Thanks!

     

    • v-xiaotang's avatar
      v-xiaotang
      Community Support
      4 years ago

      Hi danifdez 

      Thanks for your reply.

      Maybe you can share some data with me and tell me how the first value should be calculated and how the other values should be calculated (there should be two different sets of calculation logic here), then I can turn it into a dax expression.

       

       

      Best Regards,

      Community Support Team _Tang

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