A table of multiple values was supplied where a single value was expected.

[Wajahat_Kayani]

Error :- A table of multiple values was supplied where a single value was expected.

How can i get the expected result shown below using dax.

DAX Query :-

SalesTable =

VAR Brand = VALUES('Product'[Brand])

VAR Category = VALUES('Product'[Category])

VAR ProductName = VALUES('Product'[Product Name])

VAR Qty = CALCULATE(SUM(Sales[Quantity]))

VAR UnitPrice = CALCULATE(SUM(Sales[Unit Price]))

VAR Result =

    SUMMARIZECOLUMNS(

        Sales,

        'Product',

        "Brands" , Brand,

        "Category", Category,

        "Product Name" , ProductName,

        "Qty", Qty,

        "Unit Price" , UnitPrice,

        "Amount" , Qty * UnitPrice

    )

RETURN Result

Expected Result :-

Brand	Category	Product	Qty	Unit Price	Amount
abc	aa	mango	200	3.5	720
pmo	cd	car	10	2000	20000
xyz	lc	bed	50	350	17500

[Anonymous · Accepted solution]

Hi @Wajahat_Kayani ,

You can follow the steps below to get the expected result, please find the details in the attachment.

1. Assume that there is one relationship between the table 'Product' and 'Sales' base on the field [Product Name]

2. Create a measure as below to get the amount

Amount = SUM('Sales'[Qty])*SUM('Sales'[Unit Price])

3. Create a table visual which applied the field 'Product'[Brand],'Product'[Category],'Product'[Product Name] and the field 'Sales'[Quantity],Sales[Unit Price] with the aggregation function SUM

Best Regards

[danextian]

Hi @Wajahat_Kayani ,

SUMMARIZECOLUMNS does not accept a table unlike SUMMARIZE.  Also, are you supposed to aggregate the unit price as well instead of itemizing it and m ultiplying it by qty? Anway, play around with this.

SalesTable =
VAR Qty =
    CALCULATE ( SUM ( Sales[Quantity] ) )

VAR Result =
    SUMMARIZE (
        'Product',
        'Product'[Brand],
        'Product'[Category],
        'Product'[Product Name],
        Sales[Unit Price],
        "Qty", Qty,
        "Amount", Qty *  Sales[Unit Price]
    )
RETURN
    Result

[Wajahat_Kayani]

thanks @danextian for repling but your solution didn't meet my requirement. but it works for known. 

[danextian]

Hi @Wajahat_Kayani 

Just to make sure, you are entering this as a calculated table and not as a measure, yeah?

[Wajahat_Kayani]

Hi @danextian,

Thanks for reply, 

Yes i am using calculated table.