cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ormesome Regular Visitor
Regular Visitor

DAX - max of group sums

I'm a newcomer to PowerBI and DAX, and I'm in need of help.

 

Given a dataset
Sales = (Product, Units Sold, Date)

 

I'd like to display a gauge where:
The "Value" should be the sum of all Units Sold yesterday
The "Target Value" should be the average sum of all Units Sold each Date in the time period defined by the visual level filter
The "Maximum Value" should the the maximum sum of all Units Sold each Date in the time period defined by the visual level filter.

 

I think I have 2 of 3 solved.

_first_date_of_filter = MIN('Sales'[Date])

_last_date_of_filter = MAX('Sales'[Date])

_value = SUM('Sales'[Units Sold]) / DATEDIFF([_first_date_of_filter],[_last_date_of_filter],DAY)

_target_value = CALCULATE(SUM('Sales'[Units Sold]),PREVIOUSDAY('Sales'[Date]))

 

But I can't work out how to calculate the total for each day, then to grab the maximum of those totals.

 

Any ideas?

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Datatouille Established Member
Established Member

Re: DAX - max of group sums

Hi,

 

When you are doing Time-Intelligence calculations, it is much better to have a proper (and separate) Calendar Table.

See here: https://exceleratorbi.com.au/power-pivot-calendar-tables/ 

 

Once you have this calendar table, link it to your Sales Table with the Dates column (1 to Many relationship) and use this measure:

 

 

MaxValue = MAXX( Values(Calendar[Date] ) , Calculate(Sum(Sales[Units Sold] ) ) )

or, even better, store first this measure:

 

 

TotalUnits = Sum(Sales[Units Sold] )

 and then use this one :

 

MaxValue = MAXX( Values(Calendar[Date] ) , [TotalUnits] )
1 REPLY 1
Highlighted
Datatouille Established Member
Established Member

Re: DAX - max of group sums

Hi,

 

When you are doing Time-Intelligence calculations, it is much better to have a proper (and separate) Calendar Table.

See here: https://exceleratorbi.com.au/power-pivot-calendar-tables/ 

 

Once you have this calendar table, link it to your Sales Table with the Dates column (1 to Many relationship) and use this measure:

 

 

MaxValue = MAXX( Values(Calendar[Date] ) , Calculate(Sum(Sales[Units Sold] ) ) )

or, even better, store first this measure:

 

 

TotalUnits = Sum(Sales[Units Sold] )

 and then use this one :

 

MaxValue = MAXX( Values(Calendar[Date] ) , [TotalUnits] )

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 206 members 2,331 guests
Please welcome our newest community members: