Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
vsenthil
Regular Visitor

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

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:

vsenthil_0-1718676232609.png

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?

1 ACCEPTED SOLUTION
ValtteriN
Super User
Super User

Hi,

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

ValtteriN_0-1718695580463.png

 



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:

ValtteriN_1-1718695628471.png

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/







Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
vsenthil
Regular Visitor

Thanks man, worked like a charm.

ValtteriN
Super User
Super User

Hi,

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

ValtteriN_0-1718695580463.png

 



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:

ValtteriN_1-1718695628471.png

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/







Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.