SUMX, GroupBy Date and find Max count and related Hour value

[Anonymous]

Hello All,

I have below data. 

I want to find PeakHour and PeakCount for each date.

PeakHour meaning highest number of SUM(In_Count)  for each date .

-----------------

for example - in below image I filtered data for March 01, where pickhour is 13 and Pickcount is 3318. 

so I might need 2 more columns for each date it will show pick hour and pick count. 

I undesratnd I might have to use SUMX but just cant figure our DAX for it. 

Thanks  

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario.

Table:

You may create two measures as below.

InCountPeak = 
SUMX(
    ADDCOLUMNS(
        SUMMARIZE(
            'Table',
            'Table'[Date]
        ),
    "Max_InCount",CALCULATE(MAX('Table'[In_Count]))
    ),
    [Max_InCount]
)

HourPeak = 
SUMX(
    ADDCOLUMNS(
        SUMMARIZE(
            'Table',
            'Table'[Date]
        ),
        "Max_hour",CALCULATE(MAX('Table'[Hour]))
    ),
    [Max_Hour]
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous ,

Not very clear. In case you are looking for Sum of max. Use

sumx(Summmarize(Table,Table[Date],"_max",max(Table[In_count])),[_max])

Also Refer : https://community.powerbi.com/t5/Desktop/SUM-of-AVERAGE/td-p/197013

In this case you need sum of max