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.
It sounds like you want to create a matrix visual that shows the sales orders by hour and city, as well as the revenue by city. One possible solution is to use a measure that calculates the revenue for each city using the SUMX function. For example, you can create a measure like this:
Revenue by City = SUMX(VALUES('Fact Table'[City]), CALCULATE(SUM('Fact Table'[Revenue])))
This measure will iterate over the unique values of the city column in the fact table, and calculate the sum of the revenue for each city. You can then add this measure to the matrix visual, along with the sales order measure and the hour and city columns. You should see something like this:
City 1:00 AM 2:00 AM 3:00 AM 4:00 AM 5:00 AM 6:00 AM 7:00 AM 8:00 AM 9:00 AM 10:00 AM 11:00 AM Revenue by City
| Lahore | 123 | 23 | 21 | 12 | 34 | 54 | 23 | 35 | 90 | 78 | 67 | $4,500 |
| Karachi | 45 | 67 | 89 | 12 | 34 | 56 | 78 | 90 | 12 | 34 | 56 | $3,000 |
| Islamabad | 34 | 56 | 78 | 90 | 12 | 34 | 56 | 78 | 90 | 12 | 34 | $2,500 |
I hope this helps you with your Power BI project.
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.
