Forum Discussion

LorenzoNieri's avatar
LorenzoNieri
Frequent Visitor
5 years ago
Solved

Dynamic pareto measure

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,

Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    LorenzoNieri , Try a measure like

     

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

  • LorenzoNieri's avatar
    LorenzoNieri
    Frequent Visitor
    5 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.