Forum Discussion

eryka_90's avatar
eryka_90
Helper I
2 years ago
Solved

Dax having an error

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:   # VendorBalance...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
ryan_mayu
Super User
2 years ago

eryka_90 

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

eryka_90's avatar
eryka_90
Helper I
2 years ago

ryan_mayu 

 

Yes, i want to create a measure. 

Could you assist what is the correct function to use?

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    2 years ago

    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])