Forum Discussion
Creating 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!
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!
sevenhills thank you. I appreciate the effort. However, I made a mistake while explaining my requirement due to which the proposed solution isn't giving the desired result. Therefore, I'm providing the raw data to deliver the solution as intended.
The Delivery_Type field is extremely critical while calculating NoOfOrderLines, because
- If, Delivery_Type is "Ship as available" then each so line within the order is treated independently. This implies if an order has 4 lines, then each line is treated separately and will be counted as 4 Order Lines
- If, Delivery_Type is "Full consolidation at origin" then no matter how mail lines are there in the order it will always be counted as 1 Order Line
Due to the above criteria, I create the measure NoOfOrderLines. However, I'm unable to convert this measure into a variable because I'm unsure of the combination of DAX Functions needed to do so.
I require a single measure that would deliver me the Pareto Chart as demonstrated in the video link shared.
Here is the link to the raw data as I'm unable to share in this string.
Unauthorized
Error 401
the file link is unauthorized.
Post some data - copy paste
and also what is the output you are expecting, that way anyone can help.
Looks like you are trying to consolidate orders with availability of sources either use as 1 or multiple. It should be simple calc.
I need to create a Pareto Chart.
Shared below is the measure to calculate NoOfOrderLines.
Using the NoOfOrderLines measure I need assistance with the measure to calculate the cumulative total, percentage and 90% threshold. Table data is shared below as well.
This is the measure for calculating NoOfOrderLines;
NoOfOrderLines =
VAR ShipAsAvailable = FILTER('Table (ClosedOrders)', 'Table (ClosedOrders)'[delivery_type] = "Ship as available")VAR Complete_Table = FILTER(SUMMARIZE('Table (ClosedOrders)','Table (ClosedOrders)'[order_no],'Table (ClosedOrders)'[delivery_type]), 'Table (ClosedOrders)'[delivery_type] = "Full order consolidation")ReturnCOUNTROWS(ShipAsAvailable) + COUNTROWS(Complete_Table)I need assistance with the cumulative total and percentage. I also need to create an intersection of 90%SegmentCustomer_IDOrder_IDItem_NoProduct_IDQuantityPlantDelivery_TypeProduct_GroupSC_TATItem_PriceOrder_Value
Enterprise 1611318663 1111661181 10 4D937BV 1 TX39 Full consolidation at origin CFH11 17 3805.55 30458.08 Enterprise 1611318663 1111661181 200 4D937BV 1 TX39 Full consolidation at origin CFH11 17 3805.55 30458.08 Enterprise 1611318663 1111661181 390 4D937BV 1 TX39 Full consolidation at origin CFH11 17 3805.55 30458.08 Enterprise 1611318663 1111661181 580 4D937BV 1 TX39 Full consolidation at origin CFH11 17 3805.55 30458.08 Enterprise 1611318663 1111661181 770 4D937BV 1 TX39 Full consolidation at origin CFH11 17 3805.55 30458.08 Enterprise 1611318663 1111661181 960 4D937BV 1 TX39 Full consolidation at origin CFH11 17 3805.55 30458.08 Enterprise 1611318663 1111661181 1150 4D937BV 1 TX39 Full consolidation at origin CFH11 17 3805.55 30458.08 Enterprise 1611318663 1111661181 1340 4D937BV 1 TX39 Full consolidation at origin CFH11 17 3805.55 30458.08 Corporate 1511828615 1113431261 10 2F2P2BV 1 TX39 Full consolidation at origin CFH11 333 659.74 659.74 Corporate 1511828615 1113651116 10 2F2P2BV 1 TX39 Full consolidation at origin CFH11 333 659.74 659.74 Corporate 1511828615 1113713254 10 2F2P2BV 1 TX39 Full consolidation at origin CFH11 328 659.74 659.74 Corporate 1511828615 1113757653 10 2D3R6BV 1 TX39 Full consolidation at origin CFH11 325 757.89 757.89 Corporate 1511828615 1113757655 10 2D3R6BV 1 TX39 Full consolidation at origin CFH11 325 757.89 757.89 Corporate 1611285616 1113666321 10 3ST25B-DCH 1 TX39 Ship as available CFH11 314 5547.6 5385.36 Corporate 1611285616 1114126271 10 3ST25B-DCH 1 TX39 Ship as available CFH11 301 5547.6 5578.56 Corporate 1611285616 1114168784 10 3ST25B-DCH 1 TX39 Ship as available CFH11 288 5547.6 5548.56 Corporate 1611285616 1114166163 10 3ST25B-DCH 1 TX39 Ship as available CFH11 309 5547.6 5385.36 Corporate 1611285616 1114236167 10 3ST25B-DCH 2 TX39 Ship as available CFH11 300 8895.8 3799.58 Corporate 1611318663 1114276816 10 3ST25B-DCH 1 TX39 Ship as available CFH11 289 5567.85 5409.85 Corporate 1611318663 1114276816 20 B5K53B 1 TX39 Ship as available C4A11 217 554.65 5409.85 Corporate 1611318663 1114276816 30 V1B53B-DCH 1 TX39 Ship as available C8V11 217 587.35 5409.85 Corporate 1611318663 1114281418 10 3ST25B-DCH 1 TX39 Full consolidation at origin CFH11 289 5567.85 5888.5 Corporate 1611318663 1114281418 20 B5K53B 1 TX39 Full consolidation at origin C4A11 289 554.65 5888.5 Corporate 1611318663 1114412871 10 3ST25B-DCH 1 TX39 Ship as available CFH11 283 5567.85 5409.85 Corporate 1611318663 1114412871 20 B5K53B 1 TX39 Ship as available C4A11 211 554.65 5409.85 Corporate 1611318663 1114412871 30 V1B53B-DCH 1 TX39 Ship as available C8V11 211 587.35 5409.85 Corporate 1611318663 1114412866 10 3ST25B-DCH 1 TX39 Full consolidation at origin CFH11 283 5567.85 5888.5 Corporate 1611318663 1114412866 20 B5K53B 1 TX39 Full consolidation at origin C4A11 283 554.65 5888.5 Corporate 1611285616 1114446662 10 3ST25B-DCH 1 TX39 Ship as available CFH11 281 5547.6 5578.56 Corporate 1611285616 1114462157 10 3ST25B-DCH 1 TX39 Ship as available CFH11 289 5547.6 5653.04 Enterprise 1611318663 1114568135 10 J7Z14B-B2M 1 TX39 Full consolidation at origin C9D11 220 3004.35 3544.04 Enterprise 1611318663 1114568135 20 9UV22B 1 TX39 Full consolidation at origin CGR11 220 499.88 3544.04 Enterprise 1611318663 1114568135 30 2GH31B 1 TX39 Full consolidation at origin C4A11 220 40.45 3544.04 Post 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...
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
