Forum Discussion
Helper IICreating A Pareto Chart using DAX Function Combinations
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!
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!
Super UserPost the data, DAX and other related info matching ... I am getting few errors and tried to fix as I progress. Let us start using this, a little cleaner version of yours.
Table with data:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zZZbj9owEIX/CuJ514pnPPb4sYGyqFWlaneLqq72IW1RGwkRBLS/v+Ok3HZp4zi9gESwQfh8Pj4e++Fh+HK5na9X63IzH14NtdUaNVuLoSMvK1+wDp1MHmbs0eWz0JX3/Xv08jH5tlgMPlXLTbUoPxfbsloOiu2gWpdfyqX8PJpMdT2AkwdyRoootDJDrDIePl5FM0B2ARDoLwCC+AIgnLsACG8vAEJrugQKNH+TYlStV9W62NYQJJrAVlMDgQY12H2dgAm8hUQExDAxS145c2i0AlgKVvxHAKcRyPwRAOAkAHKWcA8wxlubDBDGlPEU+0MjDoD+IYAEH5hss+whA7IpYB9CvLsHyq/Ho+kpw93XcjUoNoPie1Euio+L+bGwDo4TGafCoIRMCm2rspHwg+unnOkTZXKsKEbZsmPTRxmYT5VNrLK0sd+cfZLbgNJ2Z5UhVjn8k9mTCpNH572iM2XuSa014Cwfqkya282crUQ6hJxM5kMrXhqCdE6vCfMIWfOika0ru1itbJoqBtWZzkU1csI8O1Zmp7CbMkvEuM3qyILyzHVmCXkHiF+a3gaw8/8nwM7/SH0pLNyvrgBjWt720ol500l526v2yJtOyltQtq1bOzpvT1zvsN72t5s8Om8YmbeTymqMnKHQL29p55ixoOl8Te9YWXfKchlSmWm/QBuSEof7e8sr90Gb/DqHN92M9+OGol45OV9Mkz8kYzpi1CP4dzOAjmt/c3uEYORIq0/2BIJ688HNFHVi+hqCTJkTCx5/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Segment = _t, Customer_ID = _t, Order_ID = _t, Item_No = _t, Product_ID = _t, Quantity = _t, Plant = _t, Delivery_Type = _t, Product_Group = _t, SC_TAT = _t, Item_Price = _t, Order_Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Segment", type text}, {"Customer_ID", Int64.Type}, {"Order_ID", Int64.Type}, {"Item_No", Int64.Type}, {"Product_ID", type text}, {"Quantity", Int64.Type}, {"Plant", type text}, {"Delivery_Type", type text}, {"Product_Group", type text}, {"SC_TAT", Int64.Type}, {"Item_Price", type number}, {"Order_Value", type number}})
in
#"Changed Type"
Measure "NoOfOrderLines"
NoOfOrderLines =
VAR ShipAsAvailable = FILTER('Table', 'Table'[Delivery_Type] = "Ship as available")
VAR Complete_Table = FILTER(SUMMARIZE('Table','Table'[Order_ID],
'Table'[Delivery_Type]), 'Table'[Delivery_Type] = "Full consolidation at origin") -- "Full order consolidation"
Return
COUNTROWS(ShipAsAvailable) + COUNTROWS(Complete_Table)
I am getting error when I tried the measure (which I replied and posted based on your original code)...
NoOfOrderLines running total in Days =
CALCULATE(
'Table'[NoOfOrderLines],
FILTER(
ALLSELECTED('Table'[Days]),
ISONORAFTER('Table'[Days], MAX('Table'[Days]), DESC)
)
)
Model view
Can you post what is days here ? I dont see the column here...
Helper IIFull 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 |
- JajatiDev
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
- sevenhills
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!
- JajatiDev
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%.