Pareto Dax Formula, dynamic and filtered

[LucaBr]

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

[sio2Graphs]

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 

[LucaBr]

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

[sio2Graphs]

Hi.

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

[BeaBF · Accepted solution]

@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

[LucaBr]

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

[BeaBF]

@LucaBrYou are right! You did it right!

BBF

[LucaBr]

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