Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
KristinG
New Member

Combine data from multiple columns

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 1Currency 1Material value 2Currency 2Material value 3Currency 3
123House 1235USD345EURNULLNULL
321Door340EUR2000YEN50USD
231Window230USD120SEK300EUR

 

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! 

 

1 ACCEPTED SOLUTION
v-angzheng-msft
Community Support
Community Support

 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:

v-angzheng-msft_0-1621590208018.jpeg

Visual:

v-angzheng-msft_1-1621590208020.jpeg

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.

View solution in original post

3 REPLIES 3
v-angzheng-msft
Community Support
Community Support

 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:

v-angzheng-msft_0-1621590208018.jpeg

Visual:

v-angzheng-msft_1-1621590208020.jpeg

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

@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. 

DataZoe_0-1621437045997.png

 



Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

Fowmy
Super User
Super User

@KristinG 

Please check the attached file below my signature.

Fowmy_0-1621436707295.png

 

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.