Finding a MAXX of a SUMX column

[Coriel-11]

I have two charts comparing same data for last year & this year, but I want to fix the axis to be the same for both, so trying to find a MAXX-type measure for this.
The problem is that the year filter on the chart also then filters the MAXX.

Raw Data is like this:

Source	Country	Events	Year
Google	UK	886	2022
Yahoo	UK	45	2022
Bing	UK	75	2022
Google	Italy	100	2022
Yahoo	Italy	5	2022
Google	UK	786	2021
Yahoo	Italy	40	2021
Bing	UK	35	2021
Google	Italy	80	2021

In effect I want the MAX figure of any source combining countries and regardless of year. 
So here the figure would be "986" because the sum of the Googles for 2022 are higher than both any other source, or than that of 2022.

I've tried a lot of variations on this same basic attempt to write a measure:

Mem1 = 
var xaxistable = SUMMARIZE(
'Memberships',
'Memberships'[Source/Medium (groups)],
'Memberships'[Year],
"TempField",
SUM('Memberships'[Unique Events])
)

Return 
CALCULATE(MAXX(MEM1,[TempField]),ALL(Memberships[Year])) 

But this doesn't work. The Year on the chart's filter always chooses the max figure for that year, not the max for any year.
FWIW it's similarly variable if I put it into a table or matrix.

The weird-but-vaguely-encouraging thing is that if I use the variable code to create a calculated table and then just use the Return bit as a Measure then it works just fine. But that just seems cumbersome (I already have a lot of tables)

Any ideas? I've tried variations using ALLSELECTED/ALLEXCEPT, KEEPFILTERS, SUMMARIZECOLUMNS and a bunch of others.

Thanks,

Matt 

[Coriel-11]

I eventually found a way to make the approach I was taking work, but it's overly fiddly and far less satisfactory than @Jihwan_Kim's approach because it only works if there are only two charts and the differential is one year apart, but for the sake of me being able to locate it again, or in case it helps anyone else then here it is:

Xaxis = 
var ThisYrTxt = CALCULATE(MAX('Memberships'[Year]),ALL('Memberships'[Year]))
var LastYrNum = VALUE(ThisYrTxt)-1
var LastYrTxt = FIXED(LastYrNum)
var lastx = MAXX(CALCULATETABLE(SUMMARIZE(
      'Memberships',
      'Memberships'[Source],
      'Memberships'[Year],
      "TempFieldLast",
      SUM('Memberships'[Events])
   ),Memberships[Year]=LastYrTxt,All(Memberships[Year])),[TempFieldLast])
var thisx = MAXX(CALCULATETABLE(SUMMARIZE(
      'Memberships',
      'Memberships'[Source],
      'Memberships'[Year],
      "TempFieldThis",
      SUM('Memberships'[Events])
   ),Memberships[Year]=ThisYrTxt, All(Memberships[Year])),[TempFieldThis])

Return 
if(lastx>=thisx,lastx,thisx)

As I say @Jihwan_Kim's solution is all round better, so see it above.

[Coriel-11]

Hi @Jihwan_Kim 
I'm trying to compare 2 stacked bar charts. But to do this they need to have the same x-axis maximum. So I'm trying to use conditional formatting to set that with a measure.
The data doesn't have a separate date table (if it did I think my original DAX would have been fine).

The problem is also that because each bar is filtered by year (either 2021 or 2022) when I apply the measure it operates within that filter context as well. it's not a big issue here in the dummy data because (like a fool) I've made the two values for each year too close together. But sometimes they are quite different but the axis autoscale so it doesn't show. And I have a lot of these to do each month - hence trying to automate it.
Thanks,

Matt

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your explanation.

Please try to use the measure below, and it will give the max value 986.

And this max value can be used in the X-axis maximum range.

expected result measure: =
CALCULATE (
    MAXX (
        GROUPBY (
            ADDCOLUMNS (
                ALL ( Memberships[Source], Memberships[Year] ),
                "@sumevents", CALCULATE ( SUM ( Memberships[Events] ) )
            ),
            Memberships[Source],
            "@maxevents", MAXX ( CURRENTGROUP (), [@sumevents] )
        ),
        [@maxevents]
    ),
    REMOVEFILTERS ( Memberships[Country] )
)

[Coriel-11]

Hi @Jihwan_Kim,

Thank you so, so much for this. I have been going out of my mind.
I have to admit that I don't exactly understand what you've done here - I'm going to have to go away and learn about GROUPBY & ADDCOLUMNS – they're not functions I've really used before (any pointers would be helpful). I don't even really understand how the ALL works in this context.

Thank you.

Matt

[Jihwan_Kim]

Hi,

I am not sure how your desired outcome of the visualization looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create a solution for your visualization.

expected result measure: =
IF (
    HASONEVALUE ( Memberships[Source] ),
    CALCULATE (
        MAXX (
            FILTER (
                GROUPBY (
                    ADDCOLUMNS (
                        ALL ( Memberships[Source], Memberships[Year] ),
                        "@sumevents", CALCULATE ( SUM ( Memberships[Events] ) )
                    ),
                    Memberships[Source],
                    "@maxevents", MAXX ( CURRENTGROUP (), [@sumevents] )
                ),
                Memberships[Source] = MAX ( Memberships[Source] )
            ),
            [@maxevents]
        ),
        REMOVEFILTERS ( Memberships[Country] )
    )
)

[Coriel-11]

Hi @Jihwan_Kim. Thanks for the effort here, but it doesn't quite work.
1. If I reduce it to a single value (which is how I want to use it, to be the max value for the x-axis) then it just goes to "Blank".
2. I tried removing the Hasonevalue bit, but that only seems to give me "50", which seems odd, and not what I expected given the DAX)

3. If I tried to make a new measure using your measure, it got me no further.

Any other ideas?
Matt

[Jihwan_Kim]

Hi,

Thank you for your feedback.

May I know how is your desired visualization with expected number looks like? If you can show me by picture or drawing, it will be great.

Thanks.