

Re: Filter by Measure
John delligatti Mar 22, 2018 9:59 AM (in response to Sunny Talwar )Thank you Sunny! I was able to get the pareto class to appear as a dimension, however I am stuck on one part. From the linked article the author describes the following:
If you really want it to be dynamic, it should cause no problems. However, it will mean that the classification will change the moment you select one of the Pareto groups. So a second click on a Pareto class will reduce the data sample further. This will be confusing: "Now, how many times did I click on class A?"
An alternative that I think is better is if you use {$<Product=>} as set expression (assuming that the Pareto classification is made on the field Product). Then you will have a dynamic classification that is based on all fields except the Product.
HIC
I attempted to use this in my equation below, and this causes it to error out. There are no filters selected. Is there something incorrect with my Syntax?
=Aggr(If(Rangesum(Above(Sum({1} [Total Spend])/Sum({1} total [Total Spend]),1,RowNo()))<0.8, 'Managed',
If(Rangesum(Above(Sum({1} [Total Spend])/Sum({1} total [Total Spend]),1,RowNo()))<0.9, 'Tail',
'Tail')),
({$<[Supplier Name]=>} ,(=Sum({1} [Total Spend]),Desc))
)
Thanks

Re: Filter by Measure
Sunny Talwar Mar 22, 2018 10:13 AM (in response to John delligatti)May be this is what you wanted?
=Aggr(
If(Rangesum(Above(Sum({1} [Total Spend])/Sum({1} total [Total Spend]),1,RowNo()))<0.8, 'Managed',
If(Rangesum(Above(Sum({1} [Total Spend])/Sum({1} total [Total Spend]),1,RowNo()))<0.9, 'Tail',
'Tail')),
([Supplier Name], (=Sum({$<[Supplier Name]=>} [Total Spend]),Desc))
)
or this
=Aggr(
If(Rangesum(Above(Sum({1} [Total Spend])/Sum({1} total [Total Spend]),1,RowNo()))<0.8, 'Managed',
If(Rangesum(Above(Sum({1} [Total Spend])/Sum({1} total [Total Spend]),1,RowNo()))<0.9, 'Tail',
'Tail')),
([Supplier Name], (=Sum({1} [Total Spend]),Desc))
)



Re: Filter by Measure
John delligatti Mar 22, 2018 10:36 AM (in response to John delligatti)Hi Sunny,
Both equations work but not to do exactly what I'm looking for. I'm creating a Pareto chart to show which suppliers make up the 80/20 of our spend data. I was hoping to be able to filter my numerous fields ie: Client name, category name, etc.
Both equations will only show whether a supplier is "managed" or "tail" based on % of total company spend, applying filters does not reduce the total spend so therefore the "managed" or "tail" categorization does not update, due to the {1} in the set analysis.
What I am hoping for is a dynamic function that will update Pareto classifications based on certain filters displayed in a table, but also the functionality to drop an equation into a dimension and created a "managed" or "tail" list box that will update the table, WITHOUT recalculating those values once a selection is made in the list box.
Is that possible?

Re: Filter by Measure
Sunny Talwar Mar 22, 2018 10:43 AM (in response to John delligatti)Try this in that case
=Aggr(
If(Rangesum(Above(Sum({<[Supplier Name]>} [Total Spend])/Sum({<[Supplier Name]>} total [Total Spend]),1,RowNo()))<0.8, 'Managed',
If(Rangesum(Above(Sum({<[Supplier Name]>} [Total Spend])/Sum({<[Supplier Name]>} total [Total Spend]),1,RowNo()))<0.9, 'Tail',
'Tail')),
([Supplier Name], (=Sum({<[Supplier Name]>} [Total Spend]),Desc))
)

Re: Filter by Measure
John delligatti Mar 22, 2018 11:12 AM (in response to Sunny Talwar )Works perfectly! Your "MVP" status is well deserved!! Thank you!

Re: Filter by Measure
Sunny Talwar Mar 22, 2018 11:13 AM (in response to John delligatti)Awesome, I am glad it finally worked


