Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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!
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!
Can you post your example.
I think you are using the wrong modifier. try $ instead of {1} because $ is the current selection.
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