Matrix with different grain

[shri2785]

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!

[Anonymous]

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

[123abc]

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. 

[shri2785]

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!

[123abc · Accepted solution]

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

1. Drag and drop the City column into the Rows field of the matrix.
2. Drag the hour bins (1:00 AM, 2:00 AM, etc.) from the DimCalendar table into the Columns field of the matrix.
3. Drag the Sales Orders measure into the Values field of the matrix.
4. 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.