Groupby on a calculated table

[devleena]

Hello Experts,

I am relatively new to PowerBI and having been actively using this forum to answer all my queries.

However, currently breaking my head on how to do the following.

Question I am trying to answer is : What has been the hourwise sales for a certain restaurant month on month for the last 3 year .

Data I am using for this is 

DATEKEY TABLE

1) Datetime of order

2) order_time : time component calculated from column1

3) timeperiod : time bracket of the day , say breakfast, lunch etc calculated from column 2

4) MonthY number : Year-MonthNumber column put together which is helping me filter and sort the data month wise 

5) MonthY : a textual name for the "Monthy number" column

ORDER_TABLE 

1) order number for each orders

2) Datetime of order - Linked to the datetime table in DATEKEY TABLE

3) location for each order 

4) final_total: sales made against each order

5) total_sale : a calculated column calculated as sum(final_total) against all orders

What I have done so far 

1) Logically grouped the hour-of-the-day into time brackets like Breakfast, Lunch, Supper, Dinner as we want to see it 

2) Next in a table , if I drag and drop the columns I need for my filtering , I see the following 

So, I can see the TotalSales filtered across time brackets and across months 

Now the question is, how can I have this above data on a new table so that my filtered total_sales can be a column I can use for other purpose like calculating 

1) percentage of sales of that specific time_bracket over total sales for that day 

2) percentage of sales for a specific location for a specific time_bracket over total sales for that day 

I have tried to summarise the columns 

HourSales = SUMMARIZECOLUMNS('DateKey'[Monthy number],

'Order_Table'[TimePeriod],

'Order_Table'[final_total])

and then tried to use a group by on the above but this is not returning the correct hoursales figures .

Ideally this should match to my 3rd column in the table I created ( above image )

HourGroup = GROUPBY('HourSales',HourSales[Monthy number], HourSales[TimePeriod], "TotalHourSales", SUMX(CURRENTGROUP(),'HourSales'[final_total]) )

I am sure I am doing something grossly wrong here. Looking for your advice . 

Will be happy to provide further information on this as required.

Thanks and Regards,

Dev

[v-jiascu-msft · Accepted solution]

Hi Dev,

Did you try the function SUMMARIZE? Can you share a sample please? A dummy one is enough. We can't write DAX by imagination.

Best Regards,

Dale

[devleena]

Hello Dale,

I could work out my solution using the SUMMARIZE function .

Thank you for the hint.