Forum Discussion

devleena's avatar
devleena
Frequent Visitor
8 years ago
Solved

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 

 

 

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

  • 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

     

     

2 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee

    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's avatar
      devleena
      Frequent Visitor

      Hello Dale,

       

      I could work out my solution using the SUMMARIZE function .

      Thank you for the hint.