Forum Discussion
Helper IIPareto rule for multiple columns
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.
Helper IIAnonymous
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