Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Cumulative avarage in Line Chart

I want a line chart with 3 different lines as values.

The first lane is the total revenue for a specific month.

The second line is a KPI for the revenues.

The last line should be a cumulative average which is a combined average for the selected months.

 

I use this formula but unfortunately it doesnt work:

CALCULATE([XXXXXX],FILTER(ALLSELECTED(Dim_Calendar),[Date] <= max(Dim_Calendar[Date])))
 
I get the values cumulated but it aint a cumulative average. 
 

 

I thought that the second dot of the light blue line would be in between the first and second dot of the dark blue line.
 
What am I doing wrong?
 
 

 

Need Help

5 Replies

  • Adescrit's avatar
    Adescrit
    Icon for Impactful Individual rankImpactful Individual
    2 years ago

    The first argument of your CALCULATE function should specify an AVERAGE function.

    So the DAX in a measure would be as follows. Change the word "Table" to the name of the table that contains your omzet column. 

    CALCULATE(AVERAGE( 'Table'[Omzet] ),FILTER(ALLSELECTED(Dim_Calendar),Dim_Calendar[Month] <= max(Dim_Calendar[Month])))

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      I think this is close to the solution but it is not there yet.

      I get cumulated averages but I believe that the averages are for single rows while I want them as a sum for that entire month.

      Light blue is now approx € 100 while dark blue is still €250K

      • Adescrit's avatar
        Adescrit
        Icon for Impactful Individual rankImpactful Individual
        2 years ago

        Yes you are right - my mistake. 

        Please try this:

         

        Monthly Average YTD =
AVERAGEX (
    VALUES ( 'Calendar'[Month Number] ),
    CALCULATE (
        SUM ( Sales[Omzatz] ),
        FILTER (
            ALLSELECTED ( 'Calendar' ),
            'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
        )
    )
)