Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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. 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , You can try measures like

     

    YTD Max= CALCULATE(Max(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
    Last YTD Max= CALCULATE(Max(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

     

    MTD MAX= CALCULATE(MAX(Sales[Sales Amount]),DATESMTD('Date'[Date]))
    last MTD MAX= CALCULATE(MAX(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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.