FirstSortedValue and Aggr Functions

[Sello_Mputsoe]

Hi everyone, I am working on a tasks that requires benchmarking on a particular column. The screenshot below shows a table with Purchase orders transactions. The last column is the Benchmark Country column and is based on the "Purch.Order Volume" measure (It displays the country that has the lowest purchase order volume for all rows - Since DE has the lowest volume of 850, then all rows display DE as the benchmark country.

Problem: When I make a selection on the country column for a different country, then the benchmark country value changes to the selected country. How can I ignore the selection on the benchmark country column because I do not want this column to change based on the selection of the country column?

BenchMark Country is made up from the below: 

*Sample data attached

[Or]

You could ignore the selection on Country using set analysis:

FirstSortedValue({< Country = >} total <Product> Country,
aggr(sum({< Country = >} [Purchase Order Volume]),Country,Product,[Purchase Order Nunber]))

Also, great job on including the formula and sample data,  but for future reference it'd be handy if you posted the formula as text that can be copied rather than just an image.

[Sello_Mputsoe]

@Or , thank you so much! This works well😊. One more thing though, is there a way I can hide all other rows that do not contain values after the selection has been applied?

[MayilVahanan · Accepted solution]

Hi

In the case, instead of exp, try in dimension like below

=Aggr(FirstSortedValue({< Country = >} total <Product> Country,
aggr(sum({< Country = >} [Purchase Order Volume]),Country,Product,[Purchase Order Nunber])), [Purchase Order Nunber], Product)

[Or · Accepted solution]

If you'd like to keep it as a measure, you could do something like:

If(Not Isnull(Country),

FirstSortedValue({< Country = >} total <Product> Country,
aggr(sum({< Country = >} [Purchase Order Volume]),Country,Product,[Purchase Order Nunber]))

)

Or basically any other expression that would be evaluated within the context of the line so that all other (excluded) rows return null.

[Sello_Mputsoe]

Thank you both @Or  and @MayilVahanan , both solutions work like a charm! 

Much Appreciated!