How to get Max Grand total of Sales in a month ?

[sonuojha1]

Hi Experts,

I have a below requirement. I need help to find the solution:

Granularity: The grain of data is hour.

I have a requirement to get 

1. Column Grand total ( in attached image ) each hour level.

2. Find the maximum grand total of each hour in day.

3. Find the maximum grand total in the month.

4. How can drill to detail level. ( Drill from max value on which date and time in the month)

Expected Result:- 

Here, let say maximum of value  of the day on 1st-Jan-20 is 55886(grand total), recorded on 11:00 PM,

and Maximum value of the month is recorded on 24-Jan-20 on 6:00 PM.

Source data

Date	Time	Org	Sales
1-Jan-20	1:00:00	ORG5	19000
1-Jan-20	1:00:00	ORG2	10000
1-Jan-20	1:00:00	ORG1	5112
1-Jan-20	1:00:00	ORG3	900
1-Jan-20	1:00:00	ORG4	500
1-Jan-20	2:00:00	ORG5	19001
1-Jan-20	2:00:00	ORG2	9501
1-Jan-20	2:00:00	ORG1	5109
1-Jan-20	2:00:00	ORG4	500
1-Jan-20	3:00:00	ORG5	19002
1-Jan-20	3:00:00	ORG2	10002
1-Jan-20	3:00:00	ORG1	5102
1-Jan-20	3:00:00	ORG3	2700
1-Jan-20	3:00:00	ORG4	500
1-Jan-20	4:00:00	ORG5	19003
1-Jan-20	4:00:00	ORG2	8903
1-Jan-20	4:00:00	ORG1	5107
1-Jan-20	4:00:00	ORG3	3600
1-Jan-20	4:00:00	ORG4	500
1-Jan-20	5:00:00	ORG5	19004
1-Jan-20	5:00:00	ORG2	11004
1-Jan-20	5:00:00	ORG1	5100
1-Jan-20	5:00:00	ORG3	4500
1-Jan-20	5:00:00	ORG4	500
1-Jan-20	6:00:00	ORG5	19005
1-Jan-20	6:00:00	ORG2	10505
1-Jan-20	6:00:00	ORG3	5400
1-Jan-20	6:00:00	ORG1	5093
1-Jan-20	6:00:00	ORG4	500
1-Jan-20	7:00:00	ORG5	19006
1-Jan-20	7:00:00	ORG2	13006
1-Jan-20	7:00:00	ORG3	6300
1-Jan-20	7:00:00	ORG1	5154
1-Jan-20	7:00:00	ORG4	500
1-Jan-20	8:00:00	ORG5	19007
1-Jan-20	8:00:00	ORG2	14007
1-Jan-20	8:00:00	ORG3	7200
1-Jan-20	8:00:00	ORG1	5173
1-Jan-20	8:00:00	ORG4	500
1-Jan-20	9:00:00	ORG5	19008
1-Jan-20	9:00:00	ORG2	10008
1-Jan-20	9:00:00	ORG3	8100
1-Jan-20	9:00:00	ORG1	5124
1-Jan-20	9:00:00	ORG4	500
1-Jan-20	10:00:00	ORG5	19009
1-Jan-20	10:00:00	ORG2	9509
1-Jan-20	10:00:00	ORG3	9000
1-Jan-20	10:00:00	ORG1	5127
1-Jan-20	10:00:00	ORG4	500
1-Jan-20	11:00:00	ORG5	19010
1-Jan-20	11:00:00	ORG2	10010
1-Jan-20	11:00:00	ORG3	9900
1-Jan-20	11:00:00	ORG1	5116
1-Jan-20	11:00:00	ORG4	500
1-Jan-20	12:00:00	ORG5	19011
1-Jan-20	12:00:00	ORG3	10800
1-Jan-20	12:00:00	ORG2	8911
1-Jan-20	12:00:00	ORG1	5119
1-Jan-20	12:00:00	ORG4	500
1-Jan-20	13:00:00	ORG5	19012
1-Jan-20	13:00:00	ORG3	11700
1-Jan-20	13:00:00	ORG2	11012
1-Jan-20	13:00:00	ORG1	5130
1-Jan-20	13:00:00	ORG4	500
1-Jan-20	14:00:00	ORG5	19013
1-Jan-20	14:00:00	ORG3	12600
1-Jan-20	14:00:00	ORG2	10513
1-Jan-20	14:00:00	ORG1	5131
1-Jan-20	14:00:00	ORG4	500
1-Jan-20	15:00:00	ORG5	19014
1-Jan-20	15:00:00	ORG3	13500
1-Jan-20	15:00:00	ORG2	13014
1-Jan-20	15:00:00	ORG1	5142
1-Jan-20	15:00:00	ORG4	500
1-Jan-20	16:00:00	ORG5	19015
1-Jan-20	16:00:00	ORG3	14400
1-Jan-20	16:00:00	ORG2	14015
1-Jan-20	16:00:00	ORG1	5145
1-Jan-20	16:00:00	ORG4	500
1-Jan-20	17:00:00	ORG5	19016
1-Jan-20	17:00:00	ORG3	15300
1-Jan-20	17:00:00	ORG2	14016
1-Jan-20	17:00:00	ORG1	5164
1-Jan-20	17:00:00	ORG4	500
1-Jan-20	18:00:00	ORG5	19017
1-Jan-20	18:00:00	ORG3	16200
1-Jan-20	18:00:00	ORG2	10017
1-Jan-20	18:00:00	ORG1	5183
1-Jan-20	18:00:00	ORG4	500
1-Jan-20	19:00:00	ORG5	19018
1-Jan-20	19:00:00	ORG3	17100
1-Jan-20	19:00:00	ORG2	9518
1-Jan-20	19:00:00	ORG1	5166
1-Jan-20	19:00:00	ORG4	500
1-Jan-20	20:00:00	ORG5	19019
1-Jan-20	20:00:00	ORG3	18000
1-Jan-20	20:00:00	ORG2	10019
1-Jan-20	20:00:00	ORG1	5183
1-Jan-20	20:00:00	ORG4	500
1-Jan-20	21:00:00	ORG5	19020
1-Jan-20	21:00:00	ORG3	18900
1-Jan-20	21:00:00	ORG2	8920
1-Jan-20	21:00:00	ORG1	5126
1-Jan-20	21:00:00	ORG4	500
1-Jan-20	22:00:00	ORG3	19800
1-Jan-20	22:00:00	ORG5	19021
1-Jan-20	22:00:00	ORG2	11021
1-Jan-20	22:00:00	ORG1	5143
1-Jan-20	22:00:00	ORG4	500
1-Jan-20	23:00:00	ORG3	20700
1-Jan-20	23:00:00	ORG5	19022
1-Jan-20	23:00:00	ORG2	10522
1-Jan-20	23:00:00	ORG1	5142
1-Jan-20	23:00:00	ORG4	500
2-Jan-20	0:00:00	ORG3	21600
2-Jan-20	0:00:00	ORG5	19023
2-Jan-20	0:00:00	ORG2	13023
2-Jan-20	0:00:00	ORG1	5125
2-Jan-20	0:00:00	ORG4	500
2-Jan-20	1:00:00	ORG3	22399.99
2-Jan-20	1:00:00	ORG5	19024
2-Jan-20	1:00:00	ORG2	14024
2-Jan-20	1:00:00	ORG1	5136
2-Jan-20	1:00:00	ORG4	500
2-Jan-20	2:00:00	ORG3	23299.99
2-Jan-20	2:00:00	ORG5	19025
2-Jan-20	2:00:00	ORG2	10025
2-Jan-20	2:00:00	ORG1	5133
2-Jan-20	2:00:00	ORG4	500
2-Jan-20	3:00:00	ORG3	24199.99
2-Jan-20	3:00:00	ORG5	19026
2-Jan-20	3:00:00	ORG2	9526
2-Jan-20	3:00:00	ORG1	5126
2-Jan-20	3:00:00	ORG4	500
2-Jan-20	4:00:00	ORG3	25099.99
2-Jan-20	4:00:00	ORG5	19027
2-Jan-20	4:00:00	ORG2	10027
2-Jan-20	4:00:00	ORG1	5131
2-Jan-20	4:00:00	ORG4	500
2-Jan-20	5:00:00	ORG3	25999.99
2-Jan-20	5:00:00	ORG5	19028
2-Jan-20	5:00:00	ORG2	8928
2-Jan-20	5:00:00	ORG1	5124
2-Jan-20	5:00:00	ORG4	500
2-Jan-20	6:00:00	ORG3	26899.99
2-Jan-20	6:00:00	ORG5	19029
2-Jan-20	6:00:00	ORG2	11029
2-Jan-20	6:00:00	ORG1	5117
2-Jan-20	6:00:00	ORG4	500
2-Jan-20	7:00:00	ORG3	27799.99
2-Jan-20	7:00:00	ORG5	19030
2-Jan-20	7:00:00	ORG2	10530
2-Jan-20	7:00:00	ORG1	5178
2-Jan-20	7:00:00	ORG4	500
2-Jan-20	8:00:00	ORG3	28699.99
2-Jan-20	8:00:00	ORG5	19031
2-Jan-20	8:00:00	ORG2	13031
2-Jan-20	8:00:00	ORG1	5197
2-Jan-20	8:00:00	ORG4	500
2-Jan-20	9:00:00	ORG3	29599.99
2-Jan-20	9:00:00	ORG5	19032
2-Jan-20	9:00:00	ORG2	14032
2-Jan-20	9:00:00	ORG1	5148
2-Jan-20	9:00:00	ORG4	500
2-Jan-20	10:00:00	ORG3	30499.99
2-Jan-20	10:00:00	ORG5	19033
2-Jan-20	10:00:00	ORG2	10033
2-Jan-20	10:00:00	ORG1	5151
2-Jan-20	10:00:00	ORG4	500
2-Jan-20	11:00:00	ORG3	31399.99
2-Jan-20	11:00:00	ORG5	19034
2-Jan-20	11:00:00	ORG2	9534
2-Jan-20	11:00:00	ORG1	5140
2-Jan-20	11:00:00	ORG4	500
2-Jan-20	12:00:00	ORG3	32299.99
2-Jan-20	12:00:00	ORG5	19035
2-Jan-20	12:00:00	ORG2	10035
2-Jan-20	12:00:00	ORG1	5143
2-Jan-20	12:00:00	ORG4	500
2-Jan-20	13:00:00	ORG3	33199.99
2-Jan-20	13:00:00	ORG5	19036
2-Jan-20	13:00:00	ORG2	8936
2-Jan-20	13:00:00	ORG1	5154
2-Jan-20	13:00:00	ORG4	500
2-Jan-20	14:00:00	ORG3	34099.99
2-Jan-20	14:00:00	ORG5	19037
2-Jan-20	14:00:00	ORG2	11037
2-Jan-20	14:00:00	ORG1	5155
2-Jan-20	14:00:00	ORG4	500
2-Jan-20	15:00:00	ORG3	34999.99
2-Jan-20	15:00:00	ORG5	19038
2-Jan-20	15:00:00	ORG2	10538
2-Jan-20	15:00:00	ORG1	5166
2-Jan-20	15:00:00	ORG4	500
2-Jan-20	16:00:00	ORG3	35899.99
2-Jan-20	16:00:00	ORG5	19039
2-Jan-20	16:00:00	ORG2	13039
2-Jan-20	16:00:00	ORG1	5169
2-Jan-20	16:00:00	ORG4	500
2-Jan-20	17:00:00	ORG3	36799.99
2-Jan-20	17:00:00	ORG5	19040
2-Jan-20	17:00:00	ORG2	14040
2-Jan-20	17:00:00	ORG1	5188
2-Jan-20	17:00:00	ORG4	500
2-Jan-20	18:00:00	ORG3	37699.99
2-Jan-20	18:00:00	ORG5	19041
2-Jan-20	18:00:00	ORG2	14041
2-Jan-20	18:00:00	ORG1	5207
2-Jan-20	18:00:00	ORG4	500
2-Jan-20	19:00:00	ORG3	38599.99
2-Jan-20	19:00:00	ORG5	19042
2-Jan-20	19:00:00	ORG2	10042
2-Jan-20	19:00:00	ORG1	5190
2-Jan-20	19:00:00	ORG4	500
2-Jan-20	20:00:00	ORG3	39499.99
2-Jan-20	20:00:00	ORG5	19043
2-Jan-20	20:00:00	ORG2	9543
2-Jan-20	20:00:00	ORG1	5207
2-Jan-20	20:00:00	ORG4	500
2-Jan-20	21:00:00	ORG3	40399.99

[Anonymous]

Hi @sonuojha1 ,

I'm sorry I am not very clear on  how the detailed date and time filtered based on which Org’s maximum Sale from the screenshots you gave.

Since the maximum Sale of each Org is at a different time point, for my test ,the Matrix visualization looks like this:

If the Matrix row is date+time,the drill down could not be applied, maybe you could use drill through for Sales instead.

Is the result what you want? If you have any questions, please upload more detailed data samples and expected output.

Please do mask sensitive data before uploading.

Best Regards,

Eyelyn Qin

[sonuojha1]

@Anonymous  Thanks for Reply Eyelyn.

I am sorry , but this is not the expected result. As I mentioned, Max of grand total of all organization.

You are taking maximum value of columns, however you have to first sum the all columns value in a row and then find the maximum.

Regards,

Sonu

[Greg_Deckler]

@sonuojha1 This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

[mhossain]

@sonuojha1 

I think you can structure data little bit first, do the unpivoting/multirow, so you will have three columns

Date with time

Attribute(org1,org2 etc)

Value

Now you have multirow/vertical data, so you can create a measure like MAXX, try this and hope this makes sense.

[sonuojha1]

@mhossainThanks for reply, Yes, I have already restructured it. I tried, but not achieved expected result.