Forum Discussion

ziqh's avatar
ziqh
Frequent Visitor
8 years ago

Summarising rows in calculated table

Hi All,   Stuck on an issue that I'm almost certain is a simple one.   I have two tables of data, and I've taken them to a single calculated table via a UNION, like so: CalcTable = UNION(       ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
8 years ago

I think am confused. I see your expected output in your post, can you please post the source data of the two tables?

 

Thanks

Raj

ziqh's avatar
ziqh
Frequent Visitor
8 years ago

Hi Raj,

 

Source for the two tables is a simple ODATA query.

Data itself is a bit more complex, but boils down to "ID", "Qty A" and "Qty B":

 

The summarise function does not seem to accept any fields from the temporary table.

Also tried simply wrapping then UNION() in a SUMMARIZE() block, but the same occurred.

 

Glad for any advice!

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi ziqh,

     

    There could be two methods without SUMMARIZE. Please check out the demo in the attachment.

    Method 1 =
ADDCOLUMNS (
    DISTINCT ( UNION ( VALUES ( Table1[ID] ), VALUES ( Table2[ID] ) ) ),
    "Qty A", SUMX ( FILTER ( Table1, Table1[ID] = EARLIER ( Table1[ID] ) ), [Qty A] ),
    "Qty B", SUMX ( FILTER ( 'Table2', 'Table2'[ID] = EARLIER ( Table1[ID] ) ), [Qty B] )
)

    Method 2: create a new table full of all ids and then add two columns.

    Method 2 =
DISTINCT ( UNION ( VALUES ( Table1[ID] ), VALUES ( Table2[ID] ) ) )

    Best Regards,

    Dale