Max value an Max value count

[Anonymous]

Hello,

I need to create measures to pull the "Occupied Machines Max Value" , "Max Value repeat frequency", "Occupied Machines Second largest Value" based on "Game name" and "Game Version" . 

I need to pull the data as per the summary table from the raw data table using dax measures. Does anyone know how to attched a spreasheet as it has over 1000 rows which I can' paste on the body. I just included few rows from raw data as a sample

any help is greatly appreciated.

Thanks,

Deevs

Summary

Game Name	Game Version	Occupied Machines Max  Value	Max Value repeat frequency	Occupied Machines Second largest   Value
A	3	2	27	1
A	5	1	45	0
B	10	1	37	0
C	5	7	4	6
C	10	1	53	0
F	1	106	1	105
F	5	26	1	24

Raw Data (Sample )

Date	Day_Name	Hour_Description	Game Name	Game Version	Active Machines	Occupied Machines
17-Aug-20	Mon	06:00:00 to 06:59:59	A	5	1	0
17-Aug-20	Mon	07:00:00 to 07:59:59	A	5	1	0
17-Aug-20	Mon	08:00:00 to 08:59:59	A	5	1	0
17-Aug-20	Mon	10:00:00 to 10:59:59	A	5	1	0
17-Aug-20	Mon	02:00:00 to 02:59:59	A	5	1	0
17-Aug-20	Mon	03:00:00 to 03:59:59	A	5	1	0
17-Aug-20	Mon	04:00:00 to 04:59:59	A	5	1	0
17-Aug-20	Mon	05:00:00 to 05:59:59	A	5	1	0
18-Aug-20	Tue	06:00:00 to 06:59:59	A	5	1	0
18-Aug-20	Tue	07:00:00 to 07:59:59	A	5	1	0
18-Aug-20	Tue	08:00:00 to 08:59:59	A	5	1	0
18-Aug-20	Tue	09:00:00 to 09:59:59	A	5	1	0
18-Aug-20	Tue	22:00:00 to 22:59:59	A	5	1	0
18-Aug-20	Tue	00:00:00 to 00:59:59	A	5	1	0
18-Aug-20	Tue	01:00:00 to 01:59:59	A	5	1	0
18-Aug-20	Tue	03:00:00 to 03:59:59	A	5	1	0
18-Aug-20	Tue	04:00:00 to 04:59:59	A	5	1	0
18-Aug-20	Tue	05:00:00 to 05:59:59	A	5	1	0
19-Aug-20	Wed	06:00:00 to 06:59:59	A	5	1	0
19-Aug-20	Wed	05:00:00 to 05:59:59	A	5	1	0
20-Aug-20	Thu	06:00:00 to 06:59:59	A	5	1	0
20-Aug-20	Thu	07:00:00 to 07:59:59	A	5	1	0
20-Aug-20	Thu	08:00:00 to 08:59:59	A	5	1	0
20-Aug-20	Thu	09:00:00 to 09:59:59	A	5	1	0
20-Aug-20	Thu	03:00:00 to 03:59:59	A	5	1	0
20-Aug-20	Thu	04:00:00 to 04:59:59	A	5	1	0
21-Aug-20	Fri	07:00:00 to 07:59:59	A	5	1	0
21-Aug-20	Fri	09:00:00 to 09:59:59	A	5	1	0
21-Aug-20	Fri	10:00:00 to 10:59:59	A	5	1	0
21-Aug-20	Fri	03:00:00 to 03:59:59	A	5	1	0
21-Aug-20	Fri	04:00:00 to 04:59:59	A	5	1	0
22-Aug-20	Sat	10:00:00 to 10:59:59	A	5	1	0
22-Aug-20	Sat	04:00:00 to 04:59:59	A	5	1	0
23-Aug-20	Sun	07:00:00 to 07:59:59	A	5	1	0
23-Aug-20	Sun	08:00:00 to 08:59:59	A	5	1	0
23-Aug-20	Sun	10:00:00 to 10:59:59	A	5	1	0
23-Aug-20	Sun	23:00:00 to 23:59:59	A	5	1	0
23-Aug-20	Sun	01:00:00 to 01:59:59	A	5	1	0
23-Aug-20	Sun	04:00:00 to 04:59:59	A	5	1	0

[v-easonf-msft · Accepted solution]

Hi , @Anonymous 

According to @amitchandak  tips, I made a demo for your reference

It should be noted that there are some minor errors in  amitchandak's  formula.

Part of formula

var _max = calculate(table[Occupied Machines], allexcept(Table,Table[Game Name],[Game Version]))

should be modified as  below:

var _max=CALCULATE(MAX('Table'[Occupied Machines]),ALLEXCEPT('Table','Table'[Game Name],'Table'[Game Version]))

The result will show as below:

If your problem has been solved,  please add your reply  or  accept helpful reply above as solution to close this thread.So that others having similar concern can find the answer more easily.  If you haven't, please feel free to ask.

Best Regards,
Community Support Team _ Eason

[Anonymous]

Hi @v-easonf-msft 

Really appreciate your help on this and it worked perfectly :). Since I couldn’t get the measure to work with the earlier solution posted by amit I came up with my own solution.

Not sure it the best way but here we go. Let me know what you think. Also can you let me know how to attached a spreadsheet/pbix file as initially I wanted to attached the data sample in a spreadsheet or in a pbix file.

Second Largest Value =

CALCULATE (

    MAX ('Table'[Occupied Machines]),

    FILTER (

        'Table',

        'Table'[Occupied Machines]

            < MAX ('Table'[Occupied Machines])

    )

)

Third Largest Value =

CALCULATE (

    MAX ( 'Table'[Occupied EGMs] ),

    FILTER (

        'Table',

        'Table'[Occupied EGMs]

            < CALCULATE (

                MAX ( 'Table'[Occupied EGMs] ),

                FILTER (

                    'Table',

                    'Table'[Occupied EGMs]

                        < MAX ( 'Table'[Occupied Machines] )

                )

            )

    )

)

[amitchandak]

@Anonymous , you should able get max and second max like this

max(table[Occupied Machines])

second best =
var _max = calculate(table[Occupied Machines], allexcept(Table,Table[Game Name],[Game Version]))
return
calculate(max(table[Occupied Machines]), filter(table,table[Occupied Machines]<_max))

best count =
var _max = calculate(table[Occupied Machines], allexcept(Table,Table[Game Name],[Game Version]))
return
calculate(count(table[Occupied Machines]), filter(table,table[Occupied Machines]=_max))