Aggegate values from multiple referenced tables

[AlberC]

Sorry in advance if this is a bit basic, but I'm trying to learn Power BI and understand the proper way of doing things.

I have a dataset (csv file) containing my data in a very disorganized way (unfortunatly I can not make change request to change the data format upstream)

For example, I will have the following columns: Sale date, amount($), transaction type, description 1, description 2.

Description 1 and 2 will contain information formatted differently depending on the transaction type. For example, i will have the merchant name and sale time (seperated by a comma) in description 1 if the transaction type is a foreign sale and the merchant name in description 2 (with description 1 empty) if the transaction type is a local sale.

To shape my data, I have created as many referenced tables from the main table as there are transaction types. In each of those sub-tables, i have filtered the data to contain only the transactions for that specific table and have split description 1 or 2 in as many columns as there are items in each.

I have created a reference table containing each merchant name along with a sale category for each as well as a date table.

I have created one-to-many relationships between my date tables and sub-tables and between my reference table and sub-tables.

I would like to create a bar chart representing the aggregated $amount of each category. How would I go about doing this ?

My issue is that the sale amount is spread in multiple sub-tables and I don't know how to aggregate all those values as if coming from a single table for the visualisation.

Thanks a lot for the help.

[MAwwad]

To aggregate the sale amount from multiple referenced tables and display it in a bar chart, you can create a measure that uses the SUMX function to sum the amount for each category. Here's an example:

Aggregated Amount = SUMX( UNION(ReferencedTable1, ReferencedTable2, ReferencedTable3, ...), SUM(ReferencedTable[Amount]) )

This measure uses the UNION function to combine the data from all of your referenced tables into a single table, and the SUMX function to sum the amount for each transaction.

Once you have created this measure, you can use it in a bar chart visualization and group the data by category. To do this, you can use the "Sale Category" column from your reference table as the grouping column in your bar chart.

I hope this helps! Let me know if you have any further questions.

[AlberC]

Thanks a lot for your answer, however how do I reference the "ReferencedTable" for the sum function in the second part of the sumx ? If i understand correctly your explanation, union creates the single table but I should reference that same single union table to do the sum.

Should I therefore write: Aggregated Amount = SUMX( UNION(ReferencedTable1, ReferencedTable2, ReferencedTable3, ...), SUM(UNION(ReferencedTable1, ReferencedTable2, ReferencedTable3, ...)[Amount]) ) ?

Otherwise I thought I could create a consolidated table. But I'm not sure it's best practice as I have split the original table and now would be remerging it (after treatment)

Thanks for the help