lookup DAX

[ktt777]

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.

[Anonymous]

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

[ktt777]

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 · Accepted solution]

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!!

[shafiz_p]

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