Converting a calculated column to a measure

[LewisB]

Hi all

I am trying to convert a calculated column into a measure. With this measure, I will then be able to do a lot more analysis etc (eg MTD, YTD sales etc). We have two sales departments, one that reports in $USD (called 'pus'), and one that reports in $NZD (called 'pnz'). The below calculated column essentially converts all our sales from our "pus" company into NZD using historical conversion rates.

The below calculated column works fine, however I am having trouble converting into a measure:

x-Updated Sales Switch = SWITCH(

    True(),

    '# Sales'[Company Code] = "pus" && '# Sales'[Invoice Date] >= DATE (2023,01,01) && '# Sales'[Invoice Date] <= DATE (2023,03,31), '# Sales'[Net Sales Amount]*1.4997,

    '# Sales'[Company Code] = "pus" && '# Sales'[Invoice Date] >= DATE (2022,10,01) && '# Sales'[Invoice Date] <= DATE (2022,12,31), '# Sales'[Net Sales Amount]*1.5302,

    '# Sales'[Company Code] = "pus" && '# Sales'[Invoice Date] >= DATE (2022,07,01) && '# Sales'[Invoice Date] <= DATE (2022,09,30), '# Sales'[Net Sales Amount]*1.4941,

    '# Sales'[Company Code] = "pus" && '# Sales'[Invoice Date] >= DATE (2022,04,01) && '# Sales'[Invoice Date] <= DATE (2022,06,30), '# Sales'[Net Sales Amount]*1.4252,

    '# Sales'[Company Code] = "pus" && '# Sales'[Invoice Date] >= DATE (2022,01,01) && '# Sales'[Invoice Date] <= DATE (2022,03,31), '# Sales'[Net Sales Amount]*1.4259,

    '# Sales'[Company Code] = "pus" && '# Sales'[Invoice Date] >= DATE (2021,10,01) && '# Sales'[Invoice Date] <= DATE (2021,12,31), '# Sales'[Net Sales Amount]*1.3992,

    '# Sales'[Company Code] = "pus" && '# Sales'[Invoice Date] >= DATE (2021,07,01) && '# Sales'[Invoice Date] <= DATE (2021,09,30), '# Sales'[Net Sales Amount]*1.3835,

    '# Sales'[Company Code] = "pus" && '# Sales'[Invoice Date] >= DATE (2021,04,01) && '# Sales'[Invoice Date] <= DATE (2021,06,30), '# Sales'[Net Sales Amount]*1.4548,

    '# Sales'[Company Code] = "pus" && '# Sales'[Invoice Date] >= DATE (2021,01,01) && '# Sales'[Invoice Date] <= DATE (2021,03,31), '# Sales'[Net Sales Amount]*1.4316,

    '# Sales'[Company Code] = "pus" && '# Sales'[Invoice Date] <= DATE (2020,12,31), '# Sales'[Net Sales Amount]*1.6667,

    '# Sales'[Net Sales Amount]

)

I am copying over the same DAX command as above into a measure, however the first problem I run into is:

'A single value for column 'Company Code' in table '# Sales' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.'

Is anyone able to assist with how to convert this calculated column into a measure?

Thank you

[amitchandak]

@LewisB , One of the ways is

Sumx('# Sales', <Your calculated column code>)

[LewisB]

Thanks @amitchandak. 

Your suggestion has fixed my 1st problem, however I am now running into a new problem. I have shortened the measure as per below just so it's easier to read in this forum. The updated measure is:

x-Updated Sales Measure = SWITCH(

    True(),

   SUMX('# Sales','# Sales'[Company Code] = "pus" && '# Sales'[Invoice Date] >= DATE (2023,01,01) && '# Sales'[Invoice Date] <= DATE (2023,03,31), '# Sales'[Net Sales Amount]*1.4997),

    SUMX('# Sales','# Sales'[Company Code] = "pus" && '# Sales'[Invoice Date] >= DATE (2022,10,01) && '# Sales'[Invoice Date] <= DATE (2022,12,31), '# Sales'[Net Sales Amount]*1.5302),

    '# Sales'[Net Sales Amount]

)

However, I am now running into this error:

Too many arguments were passed to the SUMX function. The maximum argument count for the function is 2.

Essentially I have SUMX filtering for the "PUS" company code, and then selecting between two dates, to then return the 'Net Sales Amount' multiplied by a set amount. I can't find a way to reduce the number of arguments required.

Is there an effieicent way to write this, so I only have two arguments and get rid of the SUMX error?

[LewisB]

I have modified my measure to:

x-Updated Sales Measure = SWITCH(

    True(),

   SELECTEDVALUE('# Sales'[Company Code]) = "pus" && SELECTEDVALUE('# Sales'[Invoice Date]) >= DATE(2023,01,01) && SELECTEDVALUE('# Sales'[Invoice Date]) <= DATE(2023,03,31), SUMX('# Sales', '# Sales'[Net Sales Amount] * 1.4997),

    SELECTEDVALUE('# Sales'[Company Code]) = "pus" && SELECTEDVALUE('# Sales'[Invoice Date]) >= DATE(2022,10,01) && SELECTEDVALUE('# Sales'[Invoice Date]) <= DATE(2022,12,31), SUMX('# Sales', '# Sales'[Net Sales Amount] * 1.8888),

    SUM('# Sales'[Net Sales Amount])

)

However, it is not producing the correct results (it is not multiplying by the specified amount - it is essentially skipping the 1st two Switch options, and just going to the last one). I have double checked, and I have the correct company & dates selected on my filters. Can you see why this isn't working?