Need help adjusting a Top/Bottom percentage by sales measure

Adfc14 — Sun, 27 Oct 2024 23:52:38 GMT

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.

Re: Need help adjusting a Top/Bottom percentage by sales measure

Qrishna — Mon, 28 Oct 2024 06:52:29 GMT

Please provide some sample data and expected output

topic Need help adjusting a Top/Bottom percentage by sales measure in App Development

Need help adjusting a Top/Bottom percentage by sales measure

Re: Need help adjusting a Top/Bottom percentage by sales measure