Line graph with each month in the last quarter plus the total of three months

[vsenthil]

I have a requirement to show sales ratios for every month in the given quarter and the total value in the same line of the line graph. Something like this:

I know it does not make sense to show individial months and the quaterly value in the same line. But this the requirement. Any idea on how to do this?

[vsenthil]

Thanks man, worked like a charm.

[ValtteriN · Accepted solution]

Hi,

Quite peculiar request, here is one way to do this:
Test data:

1. Add the following column to your calendar table:

Month and total =

VAR _Q = QUARTER([Date])

var _QuarterEndDate = ENDOFQUARTER('Calendar'[Date])

var _MF  =FORMAT([Date],"MMM")

RETURN

IF([Date]=_QuarterEndDate,

"Total Q"&_Q,

_MF)

2. Measure:

Measure 27 =

var _date = MAX('Calendar'[Date])

var _M = MONTH(_date)

VAR _Q = QUARTER(_date)

var _Y = YEAR(_date)

var _QuarterEndDate = ENDOFQUARTER('Calendar'[Date])

RETURN

IF(max('Calendar'[Date])=_QuarterEndDate,

CALCULATE(AVERAGE('Table (39)'[Value]),ALL('Calendar'),QUARTER('Calendar'[Date])=_Q,YEAR('Calendar'[Date])=_Y),

CALCULATE(AVERAGE('Table (39)'[Value]),ALL('Calendar'),MONTH('Calendar'[Date])=_M,YEAR('Calendar'[Date])=_Y))

End result:

If you want to add also year to your x-axis modify the column in calendar.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/