Forum Discussion

Helper V

1 year ago

Solved

lookup DAX

Hi

i have a data as below . Rank table 1 is calculated column, ranking the spend per country.

how can i write formula to create a visual to show spend at rank 3 for each country as below :

Link to Power BI file : Lookup Test.pbix

thanks.

shafiz_p
1 year ago
To make it dynamic, change measure to capture rank by selection. Try this:

or if you want only for maximum, then you can use this:
DynamicSpendbyRank = VAR _selectedRank = MAX('Table'[Rank table 1]) VAR _result = CALCULATE( SUM('Table'[Spend]), 'Table'[Rank table 1] = _selectedRank ) RETURN _result

Hope this helps!!

4 Replies

shafiz_p
Super User
1 year ago
Hi ktt777 Try this:

Spend At Rank 3 = CALCULATE( SUM('Table'[Spend]), 'Table'[Rank table 1] = 3 )

or you can use filter visual. See image below:

Hope this helps!!
If, Please accept it as a solution!!

Best Regards,
Shahariar Hafiz
ktt777
Helper V
1 year ago
thank you. How about if i want to see the spend from the highest rank from each country, not at fixed rank 3.
- shafiz_p
  Super User
  1 year ago
  To make it dynamic, change measure to capture rank by selection. Try this:
  
  or if you want only for maximum, then you can use this:
  DynamicSpendbyRank = VAR _selectedRank = MAX('Table'[Rank table 1]) VAR _result = CALCULATE( SUM('Table'[Spend]), 'Table'[Rank table 1] = _selectedRank ) RETURN _result
  
  Hope this helps!!
Anonymous
Not applicable
1 year ago
Hi ktt777 ,

Did shafiz_p's latest workaround help you? Please accept the relpy as solution if it is helpful, thanks.

More people will benefit from it because of this.

Best Regards,

Stephen Tao