Forum Discussion

Helper II

3 years ago

Solved

Creating A Pareto Chart using DAX Function Combinations

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

sevenhills

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!

sevenhills

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!

sevenhills

Super User

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!

JajatiDev

Helper II

3 years ago

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

sevenhills

Super User

3 years ago

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

JajatiDev

Helper II

3 years ago

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

JajatiDev
Helper II
3 years ago
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

Forum Discussion

Creating A Pareto Chart using DAX Function Combinations

Related content

Tables combine

Function Library - Create and Maintain

Folder Combine Wizard creates sample file queries but does not generate final combined query

can't create a udf function

Fabric Data Warehouse - Creating Functions