Group Sales data by Year and then find peak sales

[Anonymous]

I have the following data and need the max sales grouped by year along with year in which maximum sales was done.

Expected output: 30 and 2022

[Applicable88 · Accepted solution]

@Anonymous please try if that works for you. You need two measures:

Max Sales = 
Maxx ( values (Table[Year]), CALCULATE(sum(Table[Sales])))

MaxYear = 
var MaxSales= Maxx ( values (Tabele[Year]), CALCULATE(sum(Table[Sales])))
return 

CALCULATE(max( Table[Year]), filter ( values (Table[Year]), CALCULATE(sum(Table[Sales])= MaxSales)))

Hope that helps.

Best. 

[Anonymous]

@v-rzhou-msft 

[Applicable88]

@Anonymous, if I understand correctly that you want the sum of total sales, but only sum the max of each year? then try sth. like this:

Total Sales = Sumx( Values ( Date[Year]), Max ( 'Table'Sales))

[Anonymous]

No. I want to display the total sales of the year ( For example: for 2022 sales =10+20) which has the maximum total sales (2021 has 15, 2022 has 30, 2023 has 6). The output should be 30 and year 2022

[tamerj1]

Hi @Anonymous 
How would yoy like to disply these results? For example on a card visual? or in a matrix visual where you sluce by year and in each row you want to see the sales of the year and beside it the maxumum sales over all years and beside them the year that has the maximum sales?

[Anonymous]

On a card visual

[tamerj1]

@Anonymous 
You can use the following

MAX Sales = 
MAXX ( 
    VALUES ( 'Date'[Year] ),
    [Sales] 
)

MAX Sales Year = 
TOPN ( 1, 
    VALUES ( 'Date'[Year] ),
    [Sales],
    DESC 
)

[Anonymous]

No. I want to display the total sales of the year ( For example: for 2022 sales =10+20) which has the maximum total sales (2021 has 15, 2022 has 30, 2023 has 6). The output should be 30 and year 2022

[tamerj1]

@Anonymous 
This is exactly what it does. [Sales] is actally a measure [Sales] = SUM ( Sales[Sales] )
Alternatively you can use the fllowing:

MAX Sales = 
MAXX ( 
    VALUES ( 'Date'[Year] ),
    SUM ( Sales[Sales] )
)

MAX Sales Year = 
TOPN ( 1, 
    VALUES ( 'Date'[Year] ),
    SUM ( Sales[Sales] ),
    DESC 
)

[tamerj1]

Of course [Sales] can be SUM ( Sales[Sales] )

[Anonymous]

No, it's not showing the value. A table of multiple values was supplied where a single value was expected.

[tamerj1]

Hi @Anonymous 
Please try this

MAX Sales = 
MAXX ( 
    VALUES ( 'Date'[Year] ),
    CALCULATE ( SUM ( Sales[Sales] ) )
)

MAX Sales Year = 
TOPN ( 1, 
    VALUES ( 'Date'[Year] ),
    CALCULATE ( SUM ( Sales[Sales] ) ),
    DESC 
)

[Applicable88 · Accepted solution]

@Anonymous please try if that works for you. You need two measures:

Max Sales = 
Maxx ( values (Table[Year]), CALCULATE(sum(Table[Sales])))

MaxYear = 
var MaxSales= Maxx ( values (Tabele[Year]), CALCULATE(sum(Table[Sales])))
return 

CALCULATE(max( Table[Year]), filter ( values (Table[Year]), CALCULATE(sum(Table[Sales])= MaxSales)))

Hope that helps.

Best. 

[Anonymous]

Thank you .. this works

[Applicable88 · Accepted solution]

If you want to use TOPN -Function, this might work:

Maxx(TOPN ( 1, 
    VALUES ( Table[Year] ),
    calculate(SUM ( Table[Sales] )),
    DESC 
),Table[Year])

[tamerj1]

Can please send sample file with demo data?