Forum Discussion

Flo1's avatar
Flo1
Advocate I
3 years ago
Solved

Incorrect Max value due to different sorting

Hi Community,

 

I try to create a line chart with Total Sales by Year-Month column and an additionally constant line giving the maximum of Total Sales (Global Max).

 

I have this simple data model and date table: 

 

 

I get the line chart below which is almost perfect but has a wrong (alphabetic) sort order in x axis:

Correct Max Line, wrong sort order in x axis.

If I change the sort order of my Year-Month column in my date table and sorting it by YearMonthKey, the Global Max line in my chart gets broken and I see this:

correct x axis sorting - wrong max line

My question is: what's wrong here?

 

The definiton for my Global Max measure is:

 

 

Global Max = 
MAXX(
    ALL('Date'[Year-Month]),
    [Total Sales]
)

 

 

 

Thanks for your help!

Need Help
  • Flo1's avatar
    Flo1
    3 years ago

    amitchandak Thank you for your response!
    Your solution helps!

    I found also another solution. If I sort Year-Month column by YearMonthKey it also helps to include the sort by-column in my measure like here:

    Global Max = 
MAXX(
    ALL('Date'[Year-Month], 'Date'[YearMonthKey]),
    [Total Sales]
)

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    Flo1 , Try like

     

    Global Max =
    calculate( MAXX(
    values('Date'[Year-Month]),
    [Total Sales]
    ), allselected())

    • Flo1's avatar
      Flo1
      Advocate I
      3 years ago

      amitchandak Thank you for your response!
      Your solution helps!

      I found also another solution. If I sort Year-Month column by YearMonthKey it also helps to include the sort by-column in my measure like here:

      Global Max = 
MAXX(
    ALL('Date'[Year-Month], 'Date'[YearMonthKey]),
    [Total Sales]
)