How to get values from a row and transpose it to columns.

[Krish4Work]

Hi.. I am pretty new to Power BI

I have two tables, one For the transaction Document and another for the Tender details of the transaction.

In the tender tables, for every tender it will create a row with the tender type (eg: Cash, Master Card, Visa etc) and the tender amount.

Even if a single transaction has multiple tenders (Split tenders) like cash and one of the cards, it has multiple record of rows for each tender type.

The tender table contains Transaction SID which is the reference to the Transaction Document.

My requirement is to Create coloumns according to the multiple tender type(Cash, mastercard, visa) which will contain the tender amount as values and group it according to the transaction SID.

I tried adding a custom coloumn with

Cash = LOOKUPVALUE(TENDER[AMOUNT],TENDER[DOC_SID],DOCUMENT[SID],TENDER[TENDER_TYPE],0)

but it gives an error

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

Please help me out in this.

Thanks in advance.

[tamerj1]

Hi @Krish4Work 
Do you have a relationship between the two tables? Can you please provide sample data from both tables along with the expected result?

[Krish4Work]

Hi @tamerj1

Thanks for replying!

Please find the .pbix file in this attached wetransfer link.

 https://we.tl/t-eZ5i7pp6ty 

Both the tables have relatioship ie Tender[Doc_sid] = Document[sid].

[tamerj1 · Accepted solution]

Hi @Krish4Work 
Please refer to attached sample file. Hope this is what you're looking for

Cash = 
SUMX (
    FILTER ( 
        RELATEDTABLE ( TENDER ),
        TENDER[TENDER_TYPE] = 0
    ),
    TENDER[AMOUNT]
)

[Krish4Work]

Thankyou @tamerj1  you've been a great help.

Your solution is working for me.