Pareto 80-20

MartinB

As we know, a Pareto is used to display top offenders based on their impact in the analyzed data. How can I limit a Pareto chart to only display items that are below or equal to 80% of the total?

 

There's no chart option for it, so I assume it's gonna take ETL or SQL to solve this, any ideas or guidance please? Also, this needs to be dynamic =S

 

Thanks in advance.

GrantSmith

Hi @MartinB

You're correct in that it's not possible with the Pareto plot. Because you want it to be dynamic and ETL likely isn't going to work for you and you'd need to do it on the card itself. You might be able to do something similar with a Line + Bar chart and calculate the Pareto value via a Beast Mode for the line of the card.

 

You'd need to have Window Functions available in your instance to dynamically calculate the Pareto value. In addition to the window functions you'd also need the Aggregate Filters alpha to filter your chart based on the beast mode calculated Pareto value. You can talk to your CSM about these but again, Aggregate filters is in alpha and may not always work as expected.

 

The other option is to calculate it in an ETL and lose some of the dynamic filtering abilities.

jaeW_at_Onyx

Agree with Grant.

 

By design the Pareto will do a cumulative sum of the data displayed.  So even if you filter out 20% of the value, the remainder will be the new 100%.

 

what you can do is the grouped bar and chart.

 

for the line calc, "if pct of **bleep** sum / total > .8 then .8"

if you want to do the same for order quantity you could just alter the case statement.  just be advised you'll have trailing blank space in the card that might look funny.

 

case when
sum(sum(`Order Quantity`)) over (order by `Delivery Month`) / sum(sum(`Order Quantity`)) over () > .8 then .8
else sum(sum(coalesce(`Order Quantity`,0))) over (order by `Delivery Month`) / sum(sum(coalesce(`Order Quantity`,0))) over ()
end