Dax having an error

[eryka_90]

Hi All,

How can we resolve from getting an error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value". I use below DAX measure:

# VendorBalances =
SUMMARIZE(
'Vendor All Document',
'Vendor All Document'[Vendor],
"VendorBalance",
CALCULATE(
SUMX(
'Vendor All Document',
'Vendor All Document'[Amount USD] - 'Vendor All Document'[# Total Paid Amount]
)
)
)

Thank you

[Anonymous · Accepted solution]

Hi @eryka_90 ,
Based on your description, you are trying to calculate the difference between what the provider will spend in total and what it has already spent. Where you want to show the data by each supplier. In your dax you are using the SUMMARIZE function, which always returns a table, which is why there are multiple columns reporting errors. Depending on your needs, the first thing you can do is put this dax into an expression that creates a new table. Or you can try the following dax

Update = 
CALCULATE(
    SUM('Vendor All Document'[Amount USD]),
    ALLEXCEPT(
        'Vendor All Document',
        'Vendor All Document'[Vendor]
    )
)-[# Total Paid Amount]

Fianl output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[ryan_mayu]

@eryka_90 

Do you want to create a measure? SUMMARIZE is to use DAX to create a table.

[eryka_90]

@ryan_mayu 

Yes, i want to create a measure. 

Could you assist what is the correct function to use?

[ryan_mayu]

could you pls provide the sample data and expected output?

maybe you can try 

# VendorBalances =
VAR tbl=SUMMARIZE(
'Vendor All Document',
'Vendor All Document'[Vendor],
"VendorBalance",
CALCULATE(
SUMX(
'Vendor All Document',
'Vendor All Document'[Amount USD] - 'Vendor All Document'[# Total Paid Amount]
)
)
return sum([VendorBalance])