DAX to Union two sums

[nhanser]

Hello,

I think that I may be overthinking how to do this in DAX, but any help would be musch appreicated.  What I am looking to do is combine two diffrent set of data into one table in dax.

For example, here would be the data sources that I am looking at:

           Table A
-----------------------------
group | sub-group | qty
-----------------------------
   a  |    1      |  1
   a  |    1      |  2
   a  |    2      |  1
   a  |    2      |  2

           Table B
-----------------------------
group | sub-group | qty
-----------------------------
   b  |    2      |  4
   b  |    2      |  2
   b  |    3      |  2
   b  |    3      |  2

So I have these two distinct data sources/tables.  What I need to do is union them togeather and sum the columns.

 What I need my final resuly to be is something like this:

          Results
-----------------------------
group | sub-group | qty
-----------------------------
   a  |    1      |  3
   a  |    3      |  4
   b  |    2      |  9
  

I have tried creating some measures using SUM and FILTER, but I cant seem to find the correct way to get to this result.

Thanks for any and all help in advance!

[TaylorClark · Accepted solution]

Try some calculated tables!

In Data View, choose New Table then

UnionedTable = UNION(TableA, TableB)

Then use it to create another calculated table

SummaryTable = SUMMARIZE(UnionedTable, [group], [sub-group], "Qty", SUM(UnionedTable[qty]))

Hope this helps!

[Greg_Deckler]

Use an Append query step. Create Table A query, create Table B query. Edit Table A query, add Append step and append Table B. Make sure to transform your sub-group column to Text or in your table choose to "Do not summarize". Use a Table or Matrix visualization and turn off row totals in painter for Matrix.

[nhanser]

Thanks for the reply, but unfortuantly I am modifing the data after I bring it into the data model, so I would not be able to use the append data function.

[Greg_Deckler]

I'm not clear on this, why can't you make the same modifications to the combined table? Alternatively, are you able to make the same modifications using M code versus DAX? I'd have to know more about the modifications being made, etc. to understand this I think.