% RT Broken up by multiple fields

[Anonymous]

I have a table in Power BI with data arranged like this:

ID	OrderDate	DeliveredDate	OriginType	Carrier
1189	1/1/2024	1/3/2024	X	A
4895	3/6/2024	3/20/2024	Y	B
1087	2/23/2024	3/1/2024	X	A
3889	1/31/2024	1/31/2024	Z	C

I have added a calculated column that subtracts order date from delivered date called "Order to Delivery Days". I want to create a matrix visual with "Order To Delivery Days" along the top, a hierarchy with Origin Type and Carrier along the left, and then a % running total of the total count of IDs that fall into each of those categories. I have been able to get this to a degree, but the numbers just repeat for each carrier / origin type combination rather than showing the correct picture for those. Any ideas?

[v-zhouwen-msft]

Hi @Ashish_Mathur ,thanks for the quick reply, I'll add more.

Hi @Anonymous ,

The Table data is shown below:

Please follow these steps:
1. Use the following DAX expression to create a column

Order to Delivery Days = DATEDIFF('Table'[OrderDate],'Table'[DeliveredDate],DAY)

2. Use the following DAX expression to create a measure

Measure = DIVIDE(CALCULATE(COUNT('Table'[ID]),ALL('Table'[OriginType])),CALCULATE(COUNT('Table'[ID]),ALL('Table')))

3. Final output

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you. Please see my message below. What I'd like is a running total percentage.

[Ashish_Mathur]

Hi,

Based on the table that you have shared, show the expected result very clearly.

[Anonymous]

Here's an example. I've expanded the sample set:

ID	OrderDate	DeliveredDate	OriginType	Carrier	Days (delivered date minus order date)
1189	1/1/2024	1/3/2024	X	A	2
4895	3/6/2024	3/7/2024	Y	B	1
1087	2/23/2024	2/26/2024	X	A	3
3889	1/31/2024	1/31/2024	Z	C	0
8662	2/15/2024	2/22/2024	X	D	7
2557	1/31/2024	2/4/2024	Y	A	4
9875	1/1/2024	1/6/2024	Z	C	5
3426	3/20/2024	3/26/2024	Z	B	6
5489	3/15/2024	3/15/2024	Z	B	0
7470	2/19/2024	2/27/2024	X	D	8

I can easily create this matrix from the above:

Origin Type	Carrier	0 Days	1 Day	2 Days	3 Days	4 Days	5 Days	6 Days	7 Days	8 Days	Total
X		0	0	1	1	0	0	0	2	2	6
	A	0		1	1	0	0	0	1	1	4
	D	0	0	0	0	0	0	0	1	1	2
Y		0	1	0	0	1	0	0	0	0	2
	B	0	1	0	0	0	0	0	0	0	1
	A	0	0	0	0	1	0	0	0	0	1
Z		2	0	0	0	0	1	1	0	0	4
	B	1	0	0	0	0	0	1	0	0	2
	C	1	0	0	0	0	1	0	0	0	2
Total		2	1	1	1	1	1	1	2	2	12

And I'd like to create this matrix. The percentages are running totals for each row:

Origin Type	Carrier	0 Days	1 Day	2 Days	3 Days	4 Days	5 Days	6 Days	7 Days	8 Days
X		0.0%	0.0%	16.7%	33.3%	33.3%	33.3%	33.3%	66.7%	100.0%
	A	0.0%	0.0%	25.0%	50.0%	50.0%	50.0%	50.0%	75.0%	100.0%
	D	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	50.0%	100.0%
Y		0.0%	50.0%	50.0%	50.0%	100.0%	100.0%	100.0%	100.0%	100.0%
	B	0.0%	100.0%	100.0%	100.0%	100.0%	100.0%	100.0%	100.0%	100.0%
	A	0.0%	0.0%	0.0%	0.0%	100.0%	100.0%	100.0%	100.0%	100.0%
Z		50.0%	50.0%	50.0%	50.0%	50.0%	75.0%	100.0%	100.0%	100.0%
	B	50.0%	50.0%	50.0%	50.0%	50.0%	50.0%	100.0%	100.0%	100.0%
	C	50.0%	50.0%	50.0%	50.0%	50.0%	100.0%	100.0%	100.0%	100.0%
Total		16.7%	25.0%	33.3%	41.7%	50.0%	58.3%	66.7%	83.3%	100.0%

[Ashish_Mathur · Accepted solution]

PBI file attached.

Hope this helps.

[Anonymous]

This is definitely helpful. Can you describe what the Dummy Date is doing and how it helps with the calculation?

[Ashish_Mathur]

You are welcome.  The Date table helps with using the Date Intelligence functions.

[Anonymous]

Pictures to make this more readable.

Data:

I can easily make this matrix from the data:

I would like to make this (as a matrix visual with the ability to view carrier under origin type in a hierarchy):