Forum Discussion
Matrix with different grain
revised method:
To address your requirement in Power BI, you need to create a matrix that shows sales orders at an hourly basis based on the city and also shows the revenue per city. The key is to ensure that you have the right relationship between your fact and dimension tables and then use DAX measures to aggregate data appropriately.
Here's a step-by-step guide to help you achieve this:
Step 1: Ensure Proper Data Modeling
Make sure your data model in Power BI has the following:
- A fact table (SalesOrderDetail) that contains columns like OrderDateTime, City, SalesOrderID, and NetSales.
- A date dimension table (DimCalendar) that should have a column for each hour of the day, i.e., 1:00 AM, 2:00 AM, and so on.
Step 2: Create Hourly Bins in DAX
You mentioned that you've already created an hour column. If not, you can create a calculated column or measure to extract the hour from the OrderDateTime field.
Step 3: Create DAX Measures
Measure for Sales Orders:
Sales Orders = COUNTROWS('SalesOrderDetail')
Measure for Revenue:
Revenue = SUM('SalesOrderDetail'[NetSales])
Step 4: Build the Matrix
- Drag and drop the City column into the Rows field of the matrix.
- Drag the hour bins (1:00 AM, 2:00 AM, etc.) from the DimCalendar table into the Columns field of the matrix.
- Drag the Sales Orders measure into the Values field of the matrix.
- Now, to show the revenue per city, you can drag the Revenue measure into the Values field of the matrix as well.
Step 5: Adjust Aggregation and Relationships
Ensure that relationships between your tables are correctly set up. The relationship should be based on the City and the Hour column you've created or the OrderDateTime column.
Step 6: Display Revenue Correctly
If adding both Sales Orders and Revenue in the Values field of the matrix causes issues with aggregation, you can create a separate matrix or table visual for Revenue. Alternatively, ensure that there's no conflict in the way you've structured your DAX measures.
Final Notes:
- Ensure that you've set up relationships between tables properly.
- Make sure your DAX measures are using the correct columns and are aggregating data as expected.
- Adjust formatting and visualization settings in Power BI to make the report more readable and intuitive.
By following these steps and ensuring that your data model and DAX measures are correctly structured, you should be able to create a matrix that displays sales orders at an hourly basis based on the city and also shows the revenue per city.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.
Dear 123abc, thanks for your quick reply.
Yes i have implemented the SUMX as mentioned for revenue by city but now the challenge is that the Measure cant be added to Rows of Matrix visual. If possible can you share the pbix file where u have added the Revenue by city to Matrix visual on Rows. Thanks!
revised method:
To address your requirement in Power BI, you need to create a matrix that shows sales orders at an hourly basis based on the city and also shows the revenue per city. The key is to ensure that you have the right relationship between your fact and dimension tables and then use DAX measures to aggregate data appropriately.
Here's a step-by-step guide to help you achieve this:
Step 1: Ensure Proper Data Modeling
Make sure your data model in Power BI has the following:
- A fact table (SalesOrderDetail) that contains columns like OrderDateTime, City, SalesOrderID, and NetSales.
- A date dimension table (DimCalendar) that should have a column for each hour of the day, i.e., 1:00 AM, 2:00 AM, and so on.
Step 2: Create Hourly Bins in DAX
You mentioned that you've already created an hour column. If not, you can create a calculated column or measure to extract the hour from the OrderDateTime field.
Step 3: Create DAX Measures
Measure for Sales Orders:
Sales Orders = COUNTROWS('SalesOrderDetail')
Measure for Revenue:
Revenue = SUM('SalesOrderDetail'[NetSales])
Step 4: Build the Matrix
- Drag and drop the City column into the Rows field of the matrix.
- Drag the hour bins (1:00 AM, 2:00 AM, etc.) from the DimCalendar table into the Columns field of the matrix.
- Drag the Sales Orders measure into the Values field of the matrix.
- Now, to show the revenue per city, you can drag the Revenue measure into the Values field of the matrix as well.
Step 5: Adjust Aggregation and Relationships
Ensure that relationships between your tables are correctly set up. The relationship should be based on the City and the Hour column you've created or the OrderDateTime column.
Step 6: Display Revenue Correctly
If adding both Sales Orders and Revenue in the Values field of the matrix causes issues with aggregation, you can create a separate matrix or table visual for Revenue. Alternatively, ensure that there's no conflict in the way you've structured your DAX measures.
Final Notes:
- Ensure that you've set up relationships between tables properly.
- Make sure your DAX measures are using the correct columns and are aggregating data as expected.
- Adjust formatting and visualization settings in Power BI to make the report more readable and intuitive.
By following these steps and ensuring that your data model and DAX measures are correctly structured, you should be able to create a matrix that displays sales orders at an hourly basis based on the city and also shows the revenue per city.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.