ConversionRate

[arvindjha2050]

I have two tables

Requirement is when i select Egypt and Year: 2023 and STD : STD23

It should show 100

Requirement is when i select Egypt and Year: 2023 and STD : STD24

It should show 200

Requirement is when i select Egypt and India and Year: 2023 and STD : STD24

It should show 100*2 + 50*3 = 350

[mickey64 · Accepted solution]

Step 0: I use your DATA below. (Date:yyyy/mm/dd)

'Table1'

'Table2'

Step 1: I make a 'CountryTable'. 

CountryTable = SUMMARIZE('Table1','Table1'[Country])

 Step 2: I add 2 relationships below.

Step 3: I make 3 measures below.

M_CR = MAXX(FILTER('Table1','Table1'[STD]=SELECTEDVALUE(Table1[STD])),'Table1'[CR])

M_Sales = MAXX(FILTER('Table2','Table2'[Year]=SELECTEDVALUE('Table2'[Year])),'Table2'[Sales])

M_Total = VAR SelectedTable = SUMMARIZE('CountryTable','CountryTable'[Country],"Sales",[M_Sales],"CR",[M_CR])

        RETURN SUMX(SelectedTable,[Sales]*[CR])

Step 4: I make 3 slicers, 2 tables and a card visual.

[arvindjha2050]

Thanks a lot Mickey for the same , it works

[Selva-Salimi]

Hi @arvindjha2050 ,

if your tables does not have relations and the slicers are from the first table. then, you can write a measure as follows:

measure ConversionRate :=

var cr= selectedvalues( table1[CR])

return

sumx(calculate(max(sales),filter(table2, table2 [country] = selectedvalue ( table1[country]) && table2 [year] = selectedvalue (table1[yaer])) * CR )

If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly.