topic Re: Creating A Pareto Chart using DAX Function Combinations in DAX Commands and Tips

Creating A Pareto Chart using DAX Function Combinations

JajatiDev — Mon, 03 Jul 2023 11:07:10 GMT

Hello All,

Greeting!

While exploring DAX functions, I came across this beautiful demonstration to Create A Pareto Chart In Power BI Using DAX Function Combinations from Enterprise DNA. However, I have failed to replicate the output with my data set. Therefore, looking for assistance.

The measure used for calculating;

1. 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")

Return

COUNTROWS(ShipAsAvailable) + COUNTROWS(Complete_Table)

2. TotalNoOfLines =

CALCULATE([NoOfOrderLines], ALLSELECTED('Table(ClosedOrders)'))

I need assistance with the measure to calculate the Cumulative total and the Percentage to complete the table and create the graph.

I am looking forward to hearing from you.

Days	NoOfOrderLines	Cumulative Total	TotalNoOfLines	Percentage
0	6643		12220
1	597		12220
2	201		12220
3	226		12220
4	291		12220
5	397		12220
6	300		12220
7	306		12220
8	340		12220
9	268		12220
10	236		12220
11	179		12220
12	206		12220
13	171		12220
14	199		12220
15	213		12220
16	141		12220
17	118		12220
18	170		12220
19	143		12220
20	118		12220
21	136		12220
22	114		12220
23	109		12220
24	130		12220
25	88		12220
26	96		12220
27	84		12220

Re: Creating A Pareto Chart using DAX Function Combinations

sevenhills — Mon, 03 Jul 2023 18:30:57 GMT

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!

Re: Creating A Pareto Chart using DAX Function Combinations

JajatiDev — Tue, 11 Jul 2023 18:56:45 GMT

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.

Re: Creating A Pareto Chart using DAX Function Combinations

sevenhills — Thu, 06 Jul 2023 00:58:01 GMT

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.

Re: Creating A Pareto Chart using DAX Function Combinations

JajatiDev — Tue, 11 Jul 2023 19:29:20 GMT

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")

Return

COUNTROWS(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

Re: Creating A Pareto Chart using DAX Function Combinations

sevenhills — Thu, 13 Jul 2023 16:43:26 GMT

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...

Re: Creating A Pareto Chart using DAX Function Combinations

JajatiDev — Mon, 17 Jul 2023 20:06:49 GMT

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

Re: Creating A Pareto Chart using DAX Function Combinations

JajatiDev — Tue, 18 Jul 2023 20:31:52 GMT

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

Re: Creating A Pareto Chart using DAX Function Combinations

sevenhills — Thu, 20 Jul 2023 03:23:01 GMT

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!

Re: Creating A Pareto Chart using DAX Function Combinations

JajatiDev — Fri, 21 Jul 2023 21:44:49 GMT

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%.

Re: Creating A Pareto Chart using DAX Function Combinations

Anonymous — Fri, 28 Jul 2023 02:15:05 GMT

Hi JajatiDev ,

Is your problem solved, if it is solved, you can mark the correct answer, if not, you can explain your problem in detail, we can help you better.

Best Regards,

Liu Yang

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