Dynamic pareto measure

[LorenzoNieri]

Hello,

I was thinking about Pareto charts/visuals and percent of total in a perspect of dynamics usage for the user.

Here is what I mean:

Currently, following Matt Allington blog, I found this formula to display the running total for a specific dimension, in order to create the PARETO CHART, here is the DAX CODE:

[Fatturato]= SUMX('PowerBi bi_factsales','PowerBi bi_factsales'[total])

Pareto runn tot (static ON customer_name) =

VAR vendite_cliente= [Fatturato]

VAR TOT_FT_CLIENTE=CALCULATE([Fatturato],ALL(Cliente[customer_name]))

RETURN

DIVIDE(CALCULATE([Fatturato],FILTER(ALL(Cliente[customer_name]),[Fatturato]>=vendite_cliente)),TOT_FT_CLIENTE)

So this measure will display the Running total in the current filter context only for the dimension customer_name. What if I would like the user to change the analyzed dimension and for instance put on the rows the product code dimension and I want the measure to calculate again the running total but now on the Dimension of the procuct code?

I am currently thinking of a DAX code that will do the job, anyone has some ideas or has already had this kind of need?

Have a nice day,

[v-yuaj-msft · Accepted solution]

Hi @LorenzoNieri ,

Based on your description, I did a test though not very clear to your request. I create a measure as follows.

test for pareto = 

var x1=SUMX(FILTER(ALL(tEST),[customer_name]=SELECTEDVALUE('tEST'[customer_name])),[Sales])

var x2=CALCULATE(SUM('tEST'[Sales]),ALL())

return

DIVIDE(x1,x2)

Result:

Is the result what you want?

Hope that's what you were looking for.

Best Regards,

Yuna

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

[LorenzoNieri]

Hello and thanks for the reply,

It will still depend on that Customer table, if the user changes the dimension into (for instance product) it will display wrong values...

[amitchandak]

@LorenzoNieri , Try a measure like

divide(CALCULATE([Fatturato],filter(ALL(Cliente), Cliente[customer_name] <= max(Cliente[customer_name]))),
CALCULATE([Fatturato],ALL(Cliente)))