I am trying to display the top loc that has highest sales per company. Dummy data attached. Rank will not work as I want to get the total sales. I used LOD calculation {FIXED : MAX( { FIXED [Business Num], [Business Name], [Location]: sum( [Total Sales] ) } ) }but it does not work as expected. Appreciate if someone can help.
Thanks
Your outer FIXED would need to be at the level of the Company. SPlitting it out into two steps (which you could recombine). I've assumed that business num is a unique identifier so business name isn't required.
// Total Sales by Location
{ FIXED [Business Num], [Location]: sum( [Total Sales] ) }
Get the highest of those values for each business:
// Maximum Location Sales
{ FIXED [Business Num]: MAX([Total Sales by Location]) }
Then check which of the totals matches the highest, and put that against all rows for that business:
{ FIXED [Business Num]:
MAX(IF [Total Sales by Location] = [Maximum Location Sales by Business] THEN
[Location] END
)
}
Check the logic of the steps:
Then remove Location from the view:
