Pareto rule for multiple columns

[jeyare]

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 contains "GlobalTotal" values (as SUM of all items in the column) and some specific data that I need to extract from the expected scenario defined below.

The first column contains "Name" values (string).

Expected scenario:

I need to create a visual (Donut chart) for each column based on these conditions:

- the chart will contain only values from a specific column vs the "Name" column for a legend

- it will show just the Pareto 80% (deviation +/- 5%) of the "GlobalTotal" based on the HIGHEST values in the column only

- The HIGHEST values must contain a filter for "NAME" values, which aren't FILTERED.

If there is a way how to do it better by creating a new table from the existing one that will contain just the evaluated values, it is acceptable. Maybe it will save time (because I have several similar tables and +100 columns in total).

Here is an example of how to create the New Table from the Source Table:

"Total filtered" s the sum of the Highest value in the column based on the 80% Pareto principle explained above.

Thank you for a help

[v-yetao1-msft]

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]

@v-yetao1-msft 

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