Forum Discussion

juangomez's avatar
juangomez
Helper I
6 years ago

Divide value by specific monthly value in another table

Hello   I have a table with monthly sales from 2018 to 2020 (Forecast). I have another table with multiple exchange rates for each month. - 2 different scenario for 2020 dates & another for 2019 ...
Need Help
danextian's avatar
danextian
Super User
6 years ago

That's weird.

Did you use the whole formula? As a measure? You will not be able to use LEFT ( Sales[Date], 3 ) in a measure unless wrapped in a function such as SUMX that iterates a table/table expression. Can you please post a screenshot of the formula including the error? 

juangomez's avatar
juangomez
Helper I
6 years ago

Thanks for your help.

 

I did use the whole formula but kept receiving the error.

 

I finally could get the correct result with the following code:

// Create table Forex to identify slicer filter
// in Currency Table Create column Month= month(Date) as number
// in Sales Table Create column Month= month(Date) as number

US Sales= 
SUMX (
 // Get sales according to additional Sales Scenario slicer
    FILTER(Sales,Sales[Version]='Scenario 1'[MonthFilter 1 Value]),
    DIVIDE (
        //Sales in sales table with Sales Scenario filter
        Sales[Sales],
        SUMX(
            FILTER(
                //Double Filter,:
                //First filter for the version according to slicer
                FILTER(Currency,Currency[Version]=Forex[MonthFilter FX Value]),
                // Filter for an additional column
                Currency[Month]=Sales[Month]),
                // Return Currency Rate
                Currency[to US]
            )
        )
)