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

Something like this should work for you.

Calc_table= VAR  inTempTable= UNION(
            SELECTCOLUMNS(Table1, "ID", [ID], "Qty A", [Quantity], "Qty B", 0),
            SELECTCOLUMNS(Table1, "ID", [ID], "Qty A", 0, "Qty B", [Quantity])
                )
          RETURN
          SUMMARIZE ( inTempTable, inTempTable[ID],"Total Qty A",SUM(inTempTable[QtyA]),"Total QtyB",SUM(inTempTable[QtyB]))

 

 

 

Thanks

Raj

 

ziqh's avatar
ziqh
Frequent Visitor
8 years ago

Hi Raj, thanks for the help.

 

I've tried the suggested, but when attempting to SUM(inTempTable[QtyA]), PowerBI refuses.

Error:

Table variable "inTempTable" cannot be used in this context because a base table is expected.

 

The only fields it gives me access to in the SUM() are from other tables.

 

Thanks.

  • 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