Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
LewisB
Frequent Visitor

Converting a calculated column to a measure

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

3 REPLIES 3
amitchandak
Super User
Super User

@LewisB , One of the ways is

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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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
Frequent Visitor

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?


Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors