Forum Discussion
Creating A Pareto Chart using DAX Function Combinations
- 3 years ago
Try adding these two measures:
NoOfOrderLines running total in Days = CALCULATE( SUM('Table'[NoOfOrderLines]), FILTER( ALLSELECTED('Table'[Days]), ISONORAFTER('Table'[Days], MAX('Table'[Days]), DESC) ) )% Calc = DIVIDE([NoOfOrderLines running total in Days], Max('Table'[TotalNoOfLines]))and format the % measure as
Table visual:
Pareto chart visual using Line and stacked column chart
output:
Hope it helps!
- 3 years ago
I looked into your file and tried to understand some and could not get what is target of 90%.
Providing the details of measures, tune the names and syntax for your needs.
1. Counts Full Consolidation at Origin = VAR FullOrderConsolidation = FILTER( SUMMARIZE('DataTable','DataTable'[Order_ID], 'DataTable'[Delivery_Type]), 'DataTable'[Delivery_Type] = "Full consolidation at origin") Return COUNTROWS(FullOrderConsolidation)1. Counts Ship as Available = VAR ShipAsAvailable = FILTER('DataTable', 'DataTable'[Delivery_Type] = "Ship as available") Return COUNTROWS(ShipAsAvailable)2. No of order lines = [1. Counts Ship as Available] + [1. Counts Full Consolidation at Origin]3. Cumulative Total No of order lines = CALCULATE( [2. No of order lines], FILTER( ALLSELECTED('DataTable'), 'DataTable'[SC_TAT] <= MAx('DataTable'[SC_TAT])))3. Running % CT and Total = DIVIDE( [3. Cumulative Total No of order lines], CALCULATE( [2. No of order lines], ALLSELECTED('DataTable') ), BLANK())Sample output:
Note: You have to check your requirements as SC_TAT going up to 400 values on x-axis, which is not good in my view.
Hope this helps!
Full consolidation at origin = Full order consolidation
SC_TAT is the day's field.
X-axis represents days, Y-axis represents the number of lines
here is some more data
| Customer_ID | Order_ID | Item_No | Product_ID | Quantity | Plant | Delivery_Type | Product_Group | SC_TAT | Item_Price | Order_Value |
| 1611285616 | 1115125417 | 30 | 3GM99B-DCH | 8 | TX39 | Ship as available | CFH11 | 0 | 6884.4 | 55659.6 |
| 1611285616 | 1115125422 | 10 | 3GM99B-DCH | 45 | TX39 | Ship as available | CFH11 | 0 | 35058.85 | 58589.35 |
| 1611318663 | 1115156174 | 60 | 3ST25B-DCH | 1 | TX39 | Ship as available | CFH11 | 0 | 986.56 | 5568.54 |
| 1611318663 | 1115156174 | 70 | B5K34B | 1 | TX39 | Ship as available | C4A11 | 0 | 836.38 | 5568.54 |
| 1611318663 | 1115851715 | 10 | 3PZ35B-DCH | 1 | TX39 | Full consolidation at origin | CVA11 | 0 | 474.78 | 8963.6 |
| 1611318663 | 1115851715 | 20 | D9P29B | 1 | TX39 | Full consolidation at origin | C4A11 | 0 | 558 | 8963.6 |
| 1611318663 | 1116551234 | 10 | 7PU74B-DCH | 1 | TX39 | Full consolidation at origin | CD511 | 0 | 657.58 | 657.58 |
| 1611318663 | 1116668211 | 30 | 5CM66B | 1 | TX39 | Full consolidation at origin | CIU11 | 0 | 0.08 | 9547.48 |
| 1611246327 | 1116764123 | 40 | CZ244B-DCH | 15 | TX39 | Ship as available | CGY11 | 0 | 35058.05 | 57759.97 |
| 1611246327 | 1116764123 | 50 | T2F54B | 15 | TX39 | Ship as available | CGY11 | 0 | 80763 | 57759.97 |
| 1611318663 | 1116184818 | 10 | 3QB75B-DCH | 1 | TX39 | Full consolidation at origin | CVA11 | 1 | 393.55 | 393.55 |
| 1611318663 | 1116668511 | 20 | 5CM66B | 1 | TX39 | Full consolidation at origin | CIU11 | 1 | 0.08 | 4654.57 |
| 1611318663 | 1116646327 | 10 | B5K53B | 1 | TX39 | Full consolidation at origin | C4A11 | 1 | 559.55 | 559.55 |
| 1611318663 | 1117115157 | 10 | 2GH31B | 1 | TX39 | Full consolidation at origin | C4A11 | 1 | 88.38 | 56.76 |
| 1611318663 | 1117187761 | 10 | 3ST12B-DCH | 1 | TX39 | Full consolidation at origin | CFH11 | 1 | 700 | 900 |
| 1611318663 | 1117187761 | 20 | K2H17B | 1 | TX39 | Full consolidation at origin | C4A11 | 1 | 800 | 900 |
| 1611318663 | 1117241122 | 10 | QT1F97B | 1 | TX39 | Full consolidation at origin | CVC11 | 1 | 365.3 | 365.3 |
| 1611318663 | 1117347654 | 10 | 672G2BV | 1 | TX39 | Full consolidation at origin | CFH11 | 1 | 3536.98 | 50650.94 |
| 1611318663 | 1117455173 | 10 | V1B29B-DCH | 1 | TX39 | Full consolidation at origin | C8V11 | 1 | 859.5 | 859.5 |
| 1611287657 | 1118465345 | 80 | F2B72B | 4 | TX39 | Full consolidation at origin | C4A11 | 2 | 688.59 | 6065.35 |
| 1611287657 | 1118465345 | 90 | F2B73B | 2 | TX39 | Full consolidation at origin | C4A11 | 2 | 643.7 | 6065.35 |
| 1611287657 | 1118465345 | 100 | 7GS74BV | 2 | TX39 | Full consolidation at origin | CFH11 | 2 | 4739.08 | 6065.35 |
| 1611285616 | 1118558225 | 10 | 1PU54B-DCH | 65 | TX39 | Ship as available | CFH11 | 2 | 66868.95 | 445869.86 |
| 1611285616 | 1118558225 | 20 | F2B72B | 24 | TX39 | Ship as available | C4A11 | 2 | 3548.4 | 445869.86 |
| 1611285616 | 1118558225 | 30 | 3GM99B-DCH | 85 | TX39 | Ship as available | CFH11 | 2 | 66534.85 | 445869.86 |
| 1611285616 | 1118558225 | 50 | B5K34B | 1 | TX39 | Ship as available | C4A11 | 2 | 840.84 | 445869.86 |
| 1611285616 | 1118558225 | 60 | 3ST12B-DCH | 71 | TX39 | Ship as available | CFH11 | 2 | 56789 | 445869.86 |
| 1611318663 | 1116163171 | 70 | M3D23B | 1 | TX39 | Full consolidation at origin | C2D11 | 3 | 556 | 54503.85 |
| 1611318663 | 1116163171 | 90 | G1V45B | 1 | TX39 | Full consolidation at origin | C4A11 | 3 | 855 | 54503.85 |
| 1611318663 | 1116163171 | 140 | G1V43B | 1 | TX39 | Full consolidation at origin | C4A11 | 3 | 559.43 | 54503.85 |
| 1611285616 | 1116381261 | 20 | P1B11B | 1 | TX39 | Ship as available | C4A11 | 3 | 873.66 | 3885.46 |
| 1611318663 | 1116363475 | 10 | CF424B | 1 | TX39 | Full consolidation at origin | C4A11 | 3 | 585.98 | 585.98 |
| 1611326267 | 1116466111 | 10 | 1K2P4BV | 1 | TX39 | Full consolidation at origin | CFH11 | 3 | 859.89 | 859.89 |
| 1611318663 | 1116472144 | 10 | G1V45B | 1 | TX39 | Full consolidation at origin | C4A11 | 3 | 855 | 54670 |
| 1611246266 | 1116511314 | 10 | 115S2BV | 2 | TX39 | Full consolidation at origin | CFH11 | 3 | 340.48 | 340.48 |
Hi,
I have hosted the two power bi files to facilitate my request.
ParetoChart - where I need assistance with the measures
I'm trying to replicate the measures that are captured in the other file on my data.
https://www.dropbox.com/scl/fo/882mierhieq0yc35g8epf/h?rlkey=3ws4sp4uw83m70kdc7k9slzqv&dl=0
- sevenhills3 years agoSuper User
I looked into your file and tried to understand some and could not get what is target of 90%.
Providing the details of measures, tune the names and syntax for your needs.
1. Counts Full Consolidation at Origin = VAR FullOrderConsolidation = FILTER( SUMMARIZE('DataTable','DataTable'[Order_ID], 'DataTable'[Delivery_Type]), 'DataTable'[Delivery_Type] = "Full consolidation at origin") Return COUNTROWS(FullOrderConsolidation)1. Counts Ship as Available = VAR ShipAsAvailable = FILTER('DataTable', 'DataTable'[Delivery_Type] = "Ship as available") Return COUNTROWS(ShipAsAvailable)2. No of order lines = [1. Counts Ship as Available] + [1. Counts Full Consolidation at Origin]3. Cumulative Total No of order lines = CALCULATE( [2. No of order lines], FILTER( ALLSELECTED('DataTable'), 'DataTable'[SC_TAT] <= MAx('DataTable'[SC_TAT])))3. Running % CT and Total = DIVIDE( [3. Cumulative Total No of order lines], CALCULATE( [2. No of order lines], ALLSELECTED('DataTable') ), BLANK())Sample output:
Note: You have to check your requirements as SC_TAT going up to 400 values on x-axis, which is not good in my view.
Hope this helps!
- JajatiDev3 years agoHelper II
Thanks, I'll take a look and let you know how it went.
90% would be a horizontal line parallel to the x-axis (SC_TAT) intersecting the Cumulative Percentage line. This is meant to indicate the number it takes currently to hit 90%.