Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

% RT Broken up by multiple fields

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

 

IDOrderDateDeliveredDateOriginTypeCarrier
11891/1/20241/3/2024XA
48953/6/20243/20/2024YB
10872/23/20243/1/2024XA
38891/31/20241/31/2024ZC

 

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?

Need Help

8 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

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

      IDOrderDateDeliveredDateOriginTypeCarrierDays (delivered date minus order date)
      11891/1/20241/3/2024XA2
      48953/6/20243/7/2024YB1
      10872/23/20242/26/2024XA3
      38891/31/20241/31/2024ZC0
      86622/15/20242/22/2024XD7
      25571/31/20242/4/2024YA4
      98751/1/20241/6/2024ZC5
      34263/20/20243/26/2024ZB6
      54893/15/20243/15/2024ZB0
      74702/19/20242/27/2024XD8

       

      I can easily create this matrix from the above:

       

      Origin TypeCarrier0 Days1 Day2 Days3 Days4 Days5 Days6 Days7 Days8 DaysTotal
      X 0011000226
       A0 11000114
       D0000000112
      Y 0100100002
       B0100000001
       A0000100001
      Z 2000011004
       B1000001002
       C1000010002
      Total 21111112212

       

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

       

      Origin TypeCarrier0 Days1 Day2 Days3 Days4 Days5 Days6 Days7 Days8 Days
      X 0.0%0.0%16.7%33.3%33.3%33.3%33.3%66.7%100.0%
       A0.0%0.0%25.0%50.0%50.0%50.0%50.0%75.0%100.0%
       D0.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%
       B0.0%100.0%100.0%100.0%100.0%100.0%100.0%100.0%100.0%
       A0.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%
       B50.0%50.0%50.0%50.0%50.0%50.0%100.0%100.0%100.0%
       C50.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%
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

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