Just getting started with Power BI and trying to make a simple analysis of sales using a table and a visual. My sales data granularity is by day. Any ideas how to summarize it into a weekly and monthly view.
Group by month and week in Power BI visual
- Ensure that your Date table has a month (or week) column – create one if needed.
- Locate (or define) the measure you would like to aggregate.
- Move to the Report View.
- Create a new report Page.
- Add a column chart (or other chart types) to your page.
- Assign the Month column to the X-Axis and your selected measure to the Y-Axis.
- Define a legend if needed.
Aggregate by week and month in a summary table
- Open your Power Bi file and Move to the Data tab.
- From your Ribbon’s Calculations section, hit on New Table.
- Use the Summarize DAX function to group your data accordingly. Here is an example:
Sales by Month and Week = SUMMARIZE (Orders,Dates[Month], Dates[Week Number], "Sales Amount", SUM(Orders[Amount]))
Summarize sales by month in Power BI Example
Understand the Data Model
In this example i will use a very simple Course Sales Data Model which comprises of three tables: Courses, Orders and Dates.
Here’s a screenshot:
Create a week number column in your table
As can be seen above, the Dates table contains a Month column but no specific Week column.
- In the Data View, open your Dates table.
- Create a week column using the following DAX statement:
Week Number = WEEKNUM(Dates[Course Start Date])
Group by Month in a visual
WE would like to analyze Orders Amount by group and week . We will define a simple stack column chart to support our analysis:
- Go to the Report View and Create a new Page
- Drag a stacked column chart into the canvas.
- Place the Month and Week number in the X-Axis field.
- Put the Orders Amount field in the Y-Axis.
- Now, place the Course Name field in the legend.
- Make sure to order the X-Axis chronologically instead or alphabetically, by sorting the Month values by Month name.