Forum Discussion

arvindjha2050's avatar
arvindjha2050
New Member
1 year ago
Solved

ConversionRate

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

Need Help
  • mickey64's avatar
    mickey64
    1 year ago

    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.

     

3 Replies

  • Selva-Salimi's avatar
    Selva-Salimi
    Solution Sage
    1 year ago

    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. 

     

     

  • mickey64's avatar
    mickey64
    Super User
    1 year ago

    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.