Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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
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?
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?
User | Count |
---|---|
21 | |
20 | |
15 | |
10 | |
7 |
User | Count |
---|---|
29 | |
28 | |
12 | |
12 | |
12 |