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:

 

# 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
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

     

4 Replies

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

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    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