topic Pareto Dax Formula, dynamic and filtered in DAX Commands and Tips

Pareto Dax Formula, dynamic and filtered

LucaBr — Fri, 08 Nov 2024 13:59:28 GMT

Hi all,
i do have to create a Pareto graph divided on the X by the Root problem and on the Y by cost.
the dataframe that i have is something like this:

Data Period	ORG	Cost	Comment	Action	Root	value
1/2/2024	org1	100	root1	root1	root1	0
1/3/2024	org1	814	root1	root1	root1	0
1/4/2024	org1	814	root1	root1	root1	0
1/5/2024	org1	814	root1	root1	root1	0
1/6/2024	org1	5559	root1	root1	root1	0
1/7/2024	org1	5559	root1	root1	root1	0
1/8/2024	org1	5559	root1	root1	root1	0
1/2/2024	org2	100	root1	root1	root1	0
1/3/2024	org2	100	root1	root1	root1	0
1/4/2024	org2	100	root1	root1	root1	0
1/5/2024	org2	100	root1	root1	root1	0
1/6/2024	org2	550	root1	root1	root1	0
1/7/2024	org2	550	root1	root1	root1	0
1/8/2024	org2	550	root1	root1	root1	0
1/6/2024	org3	651	root2	root2	root2	0
1/2/2024	org4	1797	root1	root1	root1	0
1/3/2024	org4	1289	root1	root1	root1	0
1/4/2024	org4	1562	root1	root1	root1	0
1/5/2024	org4	765	root1	root1	root1	0
1/6/2024	org4	1009	root1	root1	root1	0
1/7/2024	org4	1009	root1	root1	root1	0
1/8/2024	org4	1009	root1	root1	root1	0
1/8/2024	org5	120	root3	root3	root3	0
1/8/2024	org5	150	root3	root3	root3	0

i do have some filter on year, month, org
for this purpose i did create a dax formula like this:

CumulativePercentage =
var CumulativeCost =
CALCULATE(
SUM(COST[cost]),
FILTER(
ALLSELECTED(COST),
COST[cost] >= MAX(COST[cost])
)
)
VAR TotalCost =
CALCULATE(
SUMCOST[cost]),
FILTER(
ALLSELECTED(COST) ,
COST[cost] = (COST[cost])

)
)

RETURN
DIVIDE(CumulativeCost,TotalCost,0)

the problem of the formula in this way is due to the org, for different org there are the same cc and when i place this "COST[cost] >= MAX(COST[cost])" in the filter it take the highest record and does not sum the rest, so the pareto is not perfect.
how can i create the comulative for pareto considerign also this particular problem?

If you need any more information please do not hesitate to ask

Re: Pareto Dax Formula, dynamic and filtered

BeaBF — Fri, 08 Nov 2024 14:08:07 GMT

LucaBr Hi! Try with:

CumulativePercentage =
VAR TotalCost = CALCULATE(SUM(COST[Cost]), ALLSELECTED(COST))
VAR CurrentRoot = COST[Root]

VAR CostByRoot =
CALCULATE(
SUM(COST[Cost]),
FILTER(
ALLSELECTED(COST),
COST[Root] = CurrentRoot
)
)

VAR CumulativeCost =
CALCULATE(
SUM(COST[Cost]),
FILTER(
ALLSELECTED(COST),
COST[Root] <= CurrentRoot
)
)

RETURN
DIVIDE(CumulativeCost, TotalCost, 0)

BBF

Re: Pareto Dax Formula, dynamic and filtered

LucaBr — Fri, 08 Nov 2024 14:47:12 GMT

WOW, you are amazing!!
i hust have this one problem: VAR CurrentRoot = COST[Root] does give me an error:
"

A single value for column 'Root Cause' in table 'LAYER2_COST_VARIANCE_COMMENTS_C3' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
"

i did substitute with VAR CurrentRoot = SELECTEDVALUE(COST[Root])
but for the rest is working!!!

Re: Pareto Dax Formula, dynamic and filtered

BeaBF — Fri, 08 Nov 2024 14:50:58 GMT

LucaBrYou are right! You did it right!

BBF

Re: Pareto Dax Formula, dynamic and filtered

sio2Graphs — Fri, 08 Nov 2024 20:09:20 GMT

Hello,

You can use visual "Pareto+" to create a graph and table or "Pareto by sio2Graphs" to produce the graph, if you don’t want to bother with formulas.

sio2Graphs 😀

sio2graphs on AppSource

Re: Pareto Dax Formula, dynamic and filtered

LucaBr — Mon, 11 Nov 2024 09:31:35 GMT

hi i think i did find out a small problem

basically the comulative does follow the root cause alphabetical order, but i would like to follow the cost varaince.

is the problem due to this "COST[Root] <= CurrentRoot" ?

in this case the cv for COSTING is 200
test scenario is 20
test is 5

Re: Pareto Dax Formula, dynamic and filtered

LucaBr — Mon, 11 Nov 2024 09:30:07 GMT

Hi, i did try that but in the moment i do apply the filter, it does not work anymore

Re: Pareto Dax Formula, dynamic and filtered

sio2Graphs — Mon, 11 Nov 2024 19:14:43 GMT

Hi.

You don't have to use a measure for the cost.