cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## Table or Matrix visualization - Max monthly numbers, YTD Max for regions and aggregate numbers

I would like to have one table or matrix with a Sales Region, then columns for the following information:

• Max monthly rate for the region for 12/2020
• Max Monthly rate for the region for 12/2021
• Max YTD(yearly) rate for the region for 12/2020
• Max YTD(yearly) rate for the region for 12/2021
• Sum YTD (yearly) total for the region for 12/2020
• Sum YTD(Yearly) total for the region for 12/2021

Currently, I have had to have 4 different Matrixes(1 monthly, 1 peak 2020, 1 peak 2021, and 1 for aggregate numbers), side by side, repeating the Region each time, not a pretty site or very manageable.

My current YTD Max Measure reads as follows:

PeakRegionYTD = SUMX ( SUMMARIZE (TABLE, [Customer], [Region], [YearMonth].[Year], "PeakRegionYTD", MAX (TABLE[Sales])), [PeakRegionYTD]

If I then filter for the year, I get the result I want, but I cannot combine Max Month, MaxYTD, and Aggregate all together in one table.  If there a measure that I could write that could utilize MAX and YTD for current year and prior year?  I have played around with this and have been unsuccessful thus far.  I am not able to post example data or I would, so please do not ask.

1 ACCEPTED SOLUTION
Helper III

These calculations provided the max for the lowest level (which is customer, even if I choose Region), so when I have Region, it provided the customer with the month max for the year, not the max for the region.

2 REPLIES 2
Super User

@lsullivan6311 , You can try measures like

YTD Max= CALCULATE(Max(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

MTD MAX= CALCULATE(MAX(Sales[Sales Amount]),DATESMTD('Date'[Date]))

Helper III

These calculations provided the max for the lowest level (which is customer, even if I choose Region), so when I have Region, it provided the customer with the month max for the year, not the max for the region.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.