Forum Discussion
Matrix with different grain
Dear Power BI Guru's
Being new to PBI , trying to resolve on situation at my end
Below is the requirement
The Sales orders should be shown at hourly basis, based on the city and Revenue per City.
Sales order is calculated on time / hourly basis
where as Revenue at 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 | |
| City | 123 | 23 | 21 | 12 | 34 | 54 | 23 | 35 | 90 | 78 | 67 | $4,500 |
I have added the Matrix to aggregate the sales orders at hourly but when i add the Revenue the matrix fails to add.
Little background:
Fact table with Sales order detail is joined with Dim calendar
One of the transaction datetime is split into hour column ex 2023-12-20 02:38:45 PM as 02:00 PM using dax
Based on the hour column we are aggregating the sales order in matrix.
Revenue is the sum(net sales)
Please revert if more info reqquired. 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.
4 Replies
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.
Hi shri2785 ,
Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
