Top sales calculation

[kkkumar82]

Hi all,

I have used the following expression

='The spend and savings of ' &GetFieldSelections(Supplier)&' is '&  sum(Spend)&', '&sum([MPN Savings])&'

'&'Top Spend is '& firstsortedvalue(Supplier, -Aggr(sum({1}[Spend]),Commodity,Supplier))&'

'&max(aggr(sum({1}Spend),Commodity,Supplier))

in a text box to show the selected supplier and top supplier spend and it is fine when nothing is selected but when for eg

a supplier is selected the selected and top supplier are the same, to avoid this I have also applied the identifier {1} but not working.

Do I need to calculate the top supplier in the edit script or is there any alternative the front end.

Regards

KK.

[morganaaron · Accepted solution]

Hi Kiran,

I think you're simply missing a few {1)s, try inserting:

firstsortedvalue({1}Supplier..

and

Max({1}Aggr(..

and it should work!

[Anonymous]

Can you post your example.

[#4]

I think you are using the wrong modifier. try $ instead of {1} because $ is the current selection.

[kkkumar82]

Hi Aaron for the above data set , if I want to know the top three suppliers we can use concat along with aggr,

I know it, but if I want to know the sum of spend of top three suppliers and average of spend of top three suppliers

in a text box , how to do it.

Note: here I have indirect set analysis i.e. the average i cannot calculate directly by having sum(spend)/3 because

for a commodity I may not have three suppliers sometimes.

KK