Calculate MAX Sum by Product and by Time

DAX DAX Calculations
1

I have a table with a list of Product[Product Name], Product[Count] and Product[Time am/pm].
Needing help to write Dax for the MAX Product Name by Product Count and the Product Time the MAX Count occurred.
Thanks!

2

please provide your power bi file and expected results.

3

It’s confidential data and it’s too large. Can you perhaps write an example?

4

did you do a search within the forum, youtube?

5

Yep, I have received help from the forum before which has been most helpful. I’ve attached a test file. As I mentioned in my first post the metrics needed. ThanksTest Max Count and Time.pbix (69.7 KB)

6

Hi @LisaKBI1129,

Note that you haven’t marked your Date table as a date table.
Give this a go.

Total Count = SUM( 'HighwayTraffic Table'[Traffic Count] )
.

Max Value = 
VAR _MAX = [Total Count]
VAR _Result =
    CALCULATE( MAX( 'HighwayTraffic Table'[Highway] ),
        FILTER( ALLSELECTED( 'HighwayTraffic Table' ),
            _Max
        )
    )
RETURN
    _Result

.

Max Time = 
VAR _MAX = [Total Count]
VAR _Value = [Max Value]
VAR _Result =
    CALCULATE( MIN( 'HighwayTraffic Table'[Time] ),
        FILTER( ALLSELECTED( 'HighwayTraffic Table' ),
            [Highway] = _Value &&
            _MAX
        )
    )
RETURN
    _Result

I hope this is helpful

1 Like
7

Unfortunately, this doesn’t give me the Time in which the MAX Count occurred and which Highway.

“Name of Highway” “Max Count” “Time of Max Count” (peak time of travel)

8

What outcome do you expect?

9

I table with the 3 Highways listed, the Max Count per highway, and a single Timestamp in which the Max Count occurred. 3 Rows and 3 Columns

10

Hi @LisaKBI1129,

Okay thanks, give this a go.

Max Highway = 
VAR _MAX = [Total Count]
VAR _Result =
    CALCULATE( MAX( 'HighwayTraffic Table'[Highway] ),
        FILTER( 'HighwayTraffic Table',
            _Max
        )
    )
RETURN
    _Result

.

Max Count = 
CALCULATE( [Total Count],
    TOPN( 1,
        ADDCOLUMNS(
            SUMMARIZE( 'HighwayTraffic Table', 'HighwayTraffic Table'[Highway], 'HighwayTraffic Table'[Time] ),
            "@Total", [Total Count]
        ),  [@Total]
    )
)

.

Max Time = 
CALCULATE( MAX( 'HighwayTraffic Table'[Time] ),
    TOPN( 1,
        ADDCOLUMNS(
            SUMMARIZE( 'HighwayTraffic Table', 'HighwayTraffic Table'[Highway], 'HighwayTraffic Table'[Time] ),
            "@Total", [Total Count]
        ),  [@Total]
    )
)

image

3 Likes
11

This worked!!! Thank you so much!! I’ve learned a great deal!!!

1 Like