Summarize quantities across 3 fact tables

[Anonymous]

Hi All

I am looking to summarize some quantities across 3 regions, which all have similarly structured fact_tables.
IT is giving me a hard time getting access to the dimension tables, but I can create unique lists and append a new query to create a pseudo dimension table.

each table has 4+ million rows, so appending all of it might take a while.

Now in above example, lets say I have a list of affiliates related to each fact table,
how would I create a single measure of quantities to place in a visual with the affiliate dimension

Kind regards
Daniel

[Anonymous]

Just to itterate.
Each fact_table has no common ID in the sense that only a specific subset of affiliates are in each specific fact_table.
For products, they are recuring and i could merge the query.

But for other elements thar are region specific I would need to append?

I tried doing the following 

But the sum of quantities are not adding up to the same number when simply chosing 
NA and the fact_table quantity

[Anonymous · Accepted solution]

Found the solution

[Greg_Deckler]

@Anonymous - After merging into a single fact table you could right-click the query, choose reference, remove all unneeded columns and then right click your affiliate column and choose remove duplicates to get them to be unique.

If you are talking about summing across three different fact tables, you could use the technique MC Aggregations here:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Multi-Column-Aggregations-MC-Aggregations/m-p/391698#M129

Not very clear. Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Anonymous]

You can try Summarize or SummarizeColumns function to summarize data and while grouping a data use a required filter before expression in EX. Var Table A=Summarize(Filter(),Expression)

Var Table B=Summarize(Filter(),Expression)
RETURN
UNION(A,B)

[amitchandak]

@Anonymous , I think instead of merge if you can join with a common dimension and create a measure, to sum up, that can also work.

For merge, you using power query merge

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

If so, also refer:https://www.thebiccountant.com/2019/10/28/performance-tip-for-aggregations-after-joins-in-power-query-and-power-bi/