Forum Discussion
PowerBI =SUM()
Good Afternoon,
I'm looking to create a measure on PowerBi for total approved spend. I am planning on using the SUM function - this is the formula I have - Approved Spend = SUM(PnC[Total Delivery Costs (approved in business case or latest Change Request)])
The error I am getting is 'MdxScript(Model) (5, 34) Calculation error in measure measure 'PnC'[Approved Spend}: The function SUM cannot work with values of type String.
I believe the error is that in the table I do have N/A which is text and then the figures are formatted to currency so not in the correct format. I need to keep the N/A input in the table - is there a way around this?
Thanks in advance.
you can create a new column in PQ
or you can try to use DAX to create a column
=column = if('Table (2)'[Column1]="N/A", "0",'Table (2)'[Column1])then convert the column to whole number or decimalat last, you sum the new column , then you will not get the error message.
4 Replies
Hello TomFarmer24 ,
I suggest you replace N/A in POwer query ( AKA Transform data) into 0, and then change the column type into whole number, in this case you'll be able to use sum.
check how to use replace https://learn.microsoft.com/en-us/power-query/replace-values
the SUM function only works on numbers.
If you want to leave that column formatted as text for some reason, (I truly can't imagine why that would ever be the case, but it's what you've chosen), then you can use SUMX to sum by converting the value "N/A" to 0, and extract the number from the dollar amounts using VALUE.
Total Amount =
SUMX (
'PnC',
IF ( PnC[Total Delivery Costs (approved in business case or latest Change Request)] = "N/A", 0, VALUE ( PnC[Total Delivery Costs (approved in business case or latest Change Request)]) )
)You could also use IFERROR. It won't cause you problems if someone types "n/a", "Not Applicable", or "N / A" etc rather than exactly "N/A".
Total Amount =
SUMX (
'PnC',
IFERROR(VALUE(PnC[Total Delivery Costs (approved in business case or latest Change Request)] ), 0)
)
///Mediocre Power BI advice, but it's free///you can create a new column in PQ
or you can try to use DAX to create a column
=column = if('Table (2)'[Column1]="N/A", "0",'Table (2)'[Column1])then convert the column to whole number or decimalat last, you sum the new column , then you will not get the error message.Hi TomFarmer24
Without creating a new number only column, you can try this measure
test = SUMX ( ADDCOLUMNS ( SUMMARIZE ( 'Table', 'Table'[Value] ), "@value", IFERROR ( VALUE ( [Value] ), BLANK () ), "@row count", CALCULATE ( COUNTROWS ( 'Table' ) ) ), [@value] * [@row count] )Creating a new column though generally is still the more optimal approach .
