Forum Discussion

jeyare's avatar
jeyare
Icon for Helper II rankHelper II
4 years ago

Pareto rule for multiple columns

Hi there, I'm looking for a solution for this case. I have several columns on a single table. So let's called the table "Source Table". All of the columns contain Decimal values. Each column also...
DAX
Pareto Chart
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi jeyare 

Please try this formula to create a calculated table . 

Table 2 =
var _itemfilter=LOOKUPVALUE('Table'[column1],'Table'[name],"item filtered")
var _t=FILTER('Table',[column1]>=_itemfilter && [name]<>"item filtered" )
var _tTotalFilter=ROW("name","Total filtered","column1",SUMX(FILTER(_t,[name]<>"Global Total"),[column1]))
var _global=LOOKUPVALUE('Table'[column1],'Table'[name],"Global Total")
var _t80=ROW("name","80% from the Global Total","column1", _global *0.8)
var _t75=ROW("name","Deviation L(75%)","column1", _global *0.75)
var _t85=ROW("name","Deviation H(85%)","column1", _global *0.85)
return UNION(_t,_tTotalFilter, _t80,_t75,_t85)

The final result is as shown below .

I have attached my pbix file , you can refer to it .

 

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • jeyare's avatar
    jeyare
    Icon for Helper II rankHelper II
    4 years ago

    Anonymous 

    thx for the designed solution. It works just for a single column in the table. As I noted in my initial post I have table with +100columns. So I can:

    1. Clean the Source table by PowerQuery, then I don't need to implement FILTER for the "item filtered". It will save time and the coder rows.

    2. The "union" command requires - that the two tables must have the same number of columns. And here I have additional trouble achieving the success within your code.

    3. In the Target table I need just show the Pareto rows. No need to show the variables (Global Total, Total filtered, 80% ..., Deviations).

     

    But you have the right approach to achieve it. Thank you for your support

     

    Jan