Combine data from multiple columns

[KristinG]

Hello! 

I have a table that shows different products and the material value that is bought in different currencies. The value is displayed in one column with the currency in another column. Each product can be bought in max three different currencies. 

I have a table with data like this:

Product ID	Name	Material value 1	Currency 1	Material value 2	Currency 2	Material value 3	Currency 3
123	House	1235	USD	345	EUR	NULL	NULL
321	Door	340	EUR	2000	YEN	50	USD
231	Window	230	USD	120	SEK	300	EUR

I want in a visual, to see the total value in each currency where you can see the currency with the total value beside. 

Eg.

USD 1515 

EUR 985 

etc. 

The data is imported with direct query 

Thank you! 

[v-angzheng-msft · Accepted solution]

 Hi, @KristinG 

Thanks for the ideas provided by @DataZoe , I tried to make some changes to the formula

Table2 =
SUMMARIZE (
    UNION (
        SELECTCOLUMNS (
            'Table',
            "Product", 'Table'[Product ID],
            "Name", 'Table'[Name],
            "Currency", 'Table'[Currency 1],
            "Value", 'Table'[Material value 1]
        ),
        SELECTCOLUMNS (
            'Table',
            "Product", 'Table'[Product ID],
            "Name", 'Table'[Name],
            "Currency", 'Table'[Currency 2],
            "Value", 'Table'[Material value 2]
        ),
        SELECTCOLUMNS (
            'Table',
            "Product", 'Table'[Product ID],
            "Name", 'Table'[Name],
            "Currency", 'Table'[Currency 3],
            "Value", 'Table'[Material value 3]
        )
    ),
    [Currency],
    [Value]
)

Result:

Table2:

Visual:

Please refer to the attachment below for details

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataZoe]

@KristinG I think this will work for direct query, but you can create a DAX table to make a "Currency" table, then use this measure to utilize it.

DAX Currency Table:

Table Currency =

DISTINCT(

UNION(

SELECTCOLUMNS(SUMMARIZE('Table','Table'[Currency 1]),"Currency",'Table'[Currency 1]),

SELECTCOLUMNS(SUMMARIZE('Table','Table'[Currency 2]),"Currency",'Table'[Currency 2]),

SELECTCOLUMNS(SUMMARIZE('Table','Table'[Currency 3]),"Currency",'Table'[Currency 3])

)

)

Then use this measure:

Value =

var curr = SELECTEDVALUE('Table Currency'[Currency])

var m1 = CALCULATE(sum('Table'[Material value 1]),'Table'[Currency 1]=curr)

var m2 = CALCULATE(sum('Table'[Material value 2]),'Table'[Currency 2]=curr)

var m3 = CALCULATE(sum('Table'[Material value 3]),'Table'[Currency 3]=curr)

var totalm = m1 + m2 + m3

return

totalm

No relationship between the tables. 

[Fowmy]

@KristinG 

Please check the attached file below my signature.