cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
devleena Frequent Visitor
Frequent Visitor

Groupby on a calculated table

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 

 

Screen Shot 2018-06-12 at 11.03.34 AM.png

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Groupby on a calculated table

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

 

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: Groupby on a calculated table

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

 

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
devleena Frequent Visitor
Frequent Visitor

Re: Groupby on a calculated table

Hello Dale,

 

I could work out my solution using the SUMMARIZE function .

Thank you for the hint.