Finding Max of 3 values in a day for different product sales

[nagoor]

Hi Experts, 

Could you help me on the below topic, 

I Have a data where it has hour lvl sales amount per sector, i want to identify the Top 3 sales period and amount of sales in average. 

Example with my data attached: 

Combine_1 (ending) 

Take Top 3 hours

Average of Top 3 hours values 

https://www.dropbox.com/scl/fi/mdyyfpkkowgvz8ohngi5q/Raw.xlsx?dl=0&rlkey=s7bjafjh4f2h0wg9g4hxdgffp 

[v-lid-msft · Accepted solution]

Hi @nagoor ,

We can use the following measue in visual filter to show the top 3 hour (set "is 1" in visual filter)

IS TOP 3 Hour = 
IF (
    RANKX (
        CALCULATETABLE (
            DISTINCT ( 'Table'[Time] ),
            ALLSELECTED (),
            'Table'[Date] IN DISTINCT ( 'Table'[Date] ),
            'Table'[Product] IN DISTINCT ( 'Table'[Product] )
        ),
        CALCULATE ( SUM ( 'Table'[Total Sales] ) ),
        ,
        DESC,
        DENSE
    ) <= 3,
    1,
    -1
)

Date column was created by following calculated column formula:

Date = INT([Time])

Then we can Create following measure to calculate average sales based on the date and product (do not need above measure  in visual filter)

Average = AVERAGEX(TOPN(3,SUMMARIZE('Table','Table'[Time],"AllSales",SUM('Table'[Total Sales])),[AllSales],DESC),[AllSales])

If it doesn't meet your requirement, Could you please show the exact expected result based on the tables that you have shared?

By the way, PBIX file as attached.

Best regards,

[amitchandak]

Rank should help

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale