Hi,
The end goal of the measure I am working on is to be able to count the number of suppliers that account for the bottom 80% of their spend.
I was able to do so, by first creating a formula for assigning a label to the top 20% and to the bottom 80%:
Aggr(
If(Rangesum(Above(Sum({1} [Invoice Amt$])/Sum({1} total [Invoice Amt$]),1,RowNo()))<0.2, 'Top',
'Bottom'),
([Vendor Name],(=Sum({1} [Invoice Amt$]),Desc))
)
After I confirmed that the above formula was working by placing it in a table along with the Vendor Name, the Invoice Amt$, I then created a KPI to do the count of the bottom 80%:
Count({<[Vendor Name]={"=Aggr(Rangesum(Above(Sum({1} [Invoice Amt$])/Sum({1} total [Invoice Amt$]),1,RowNo()))>=0.2,([Vendor Name],(=Sum({1} [Invoice Amt$]),Desc)))"} >}
(I merely changed the <0.2 to >=0.2), which worked like a charm.
But now here's where I need your help:
I need to be able to filter by 2 dimensions, Category1 and Category2, and have the top 20% and bottom 80% still work, so that if they filter for example by category1= X, then the KPI will show the count of the Vendor Name that make up the bottom 80% of spend in that category.
Can someone help me adjust the "main" measure so I can validate results by adding to the above-mentioned table the 2 categories (Catgory1 and Category2)? And then from there I believe I can easily change the KPI measure to replicate, as I did for my current measure.
Thank you in advance.
