New Table Aggregating Column

[Anonymous]

Hi All,

Should be an easy question but I keep getting an error with the summarize feature. I have the following table that has a parent child relationship. 

Parent ID	Work Item ID	Cost
	A
A	B	10
A	C	3
A	D	5
B	E	4
B	F	4

I am trying to create a new table using SELECTCOLUMNS where I aggregate the children to a single a row, however it keeps saying I have 3 and 2 underlying data points which keeps messing up my joins. Anyone know how I can get it to the table below without doing the merge in powerquery and aggregation since it takes forever on a large dataset?

Parent ID	Cost
A	18
B	8

[bcdobbs · Accepted solution]

Issue is being caused by an empty string in Parent ID.

Try

SummaryTable = 
ADDCOLUMNS (
    CALCULATETABLE (
        DISTINCT( OriginalTable[Parent ID] ),
        NOT OriginalTable[Parent ID] = ""
    ),
    "Cost", CALCULATE ( SUM(OriginalTable[Cost] ) )
)

BLANK function (DAX) - DAX | Microsoft Docs states
Some DAX functions treat blank cells somewhat differently from Microsoft Excel. Blanks and empty strings ("") are not always equivalent, but some operations may treat them as such.

[Ashish_Mathur]

Hi,

You can create a simple table visual by dragging the Parent column and writing this measure

Measure = sum(Data[Cost])

Hope this helps.

[Anonymous]

A lot more complicated than that unfortunately. I have 4 master data management tables that join on custom keys with values such as # of points.  When I do a measure based on the joined value, it multiplies it by the number of child tasks and the totals dont add up, and as they say, the best way to stop that from happening is to bring them in as columns, which is why I was trying to create a single table with all of the costs tied to the parents and then go from there.

[bcdobbs · Accepted solution]

Issue is being caused by an empty string in Parent ID.

Try

SummaryTable = 
ADDCOLUMNS (
    CALCULATETABLE (
        DISTINCT( OriginalTable[Parent ID] ),
        NOT OriginalTable[Parent ID] = ""
    ),
    "Cost", CALCULATE ( SUM(OriginalTable[Cost] ) )
)

BLANK function (DAX) - DAX | Microsoft Docs states
Some DAX functions treat blank cells somewhat differently from Microsoft Excel. Blanks and empty strings ("") are not always equivalent, but some operations may treat them as such.

[Anonymous]

For example:

Parent ID	Work Item ID	Cost	State
	A		Open
A	B	10	Closed
A	C	3	New
A	D	5	Open
	B		Closed
B	E	4	Open
B	F	4	New

To this:

Parent ID	Cost	State
A	18	Open
B	8	Closed

[Anonymous]

As soon as I add state for example though it gives me the states of the underlying children. Is there a way to just keep everything at the Parent ID and ignore the children except for the sum of the underlying cost?

[bcdobbs]

I'm not quite sure what you mean.

I see you've modified the output example in the question. How does it know whether A is open or closed?

[Anonymous]

A variation of this I guess if you know a simpler way:

Table =
VAR A =
ADDCOLUMNS (
CALCULATETABLE (
DISTINCT( Table[Parent Work Item Id] ),
NOT Table[Parent Work Item Id] = ""
),
"Cost", CALCULATE ( SUM(Table[Cost] ) )
)

VAR B =
SELECTCOLUMNS(Table,
"Area Path",Table[Area Path],
"Category",Table[Type],
"State",Table[State])

VAR Result =
NATURALLEFTOUTERJOIN(A,B)

Return
Result

[bcdobbs]

This does what you're asking for I think. Effectively creates a single row for each exisiting combination but adds up Cost based just on Parent Id.

SummaryTable = 
ADDCOLUMNS (
    CALCULATETABLE (
        SUMMARIZE( 
            OriginalTable,
            OriginalTable[Parent ID],
            OriginalTable[State],
            OriginalTable[Area Path],
            OriginalTable[Category] ),
        NOT OriginalTable[Parent ID] = ""
    ),
    "Cost", CALCULATE ( 
                SUM( OriginalTable[Cost] ),
                ALLEXCEPT ( OriginalTable, OriginalTable[Parent Id] )
            )
)

Not sure what your next steps are with this but you could avoid the calculated table all together and just write a measure to return that cost based on current visual:

Parent Id Cost = 
    CALCULATE ( 
                SUM( OriginalTable[Cost] ),
                REMOVEFILTERS( OriginalTable ),
                VALUES (OriginalTable[Parent Id] )
            )

Used in a table visual:

(Note I'd put dummy numeric values in Area Path and Category so had to make sure they were set to not summarise.)

[bcdobbs]

Will give it some thought!

[Anonymous]

But this doesnt work because there isnt technically a column in Var A to match it on.

[Anonymous]

It's a column in ADO. Technically its a flat list of items where the Parents also Appear in the Work Item ID column so the state is tied to that. So with the example below, I essentially need to row for items with Parent ID = "" but need to sum all the Work Items that have that "Parent" listed in the Parent column. and remove them from the list. I tried to duplicate the sheet and build a connection but was much more complicated than anticipated so I was hoping to just pull the flat list of rows with the sum of children in a new table.

Parent ID	Work Item ID	Cost	State
	A		Open
A	B	10	Closed
A	C	3	New
A	D	5	Open
	B		Closed
B	E	4	Open
B	F	4	New

[Anonymous]

How would I write this if I also need to select additional columns associated to the parent such as Area Path? This is ADO if you weren't aware.

[bcdobbs · Accepted solution]

To use more than one column use SUMMARIZE instead.

SummaryTable = 
ADDCOLUMNS (
    CALCULATETABLE (
        SUMMARIZE(
            OriginalTable,  
            OriginalTable[Parent ID],
            OriginalTable[Column 2]
        ),
        NOT OriginalTable[Parent ID] == ""
    ),
    "Cost", CALCULATE ( SUM(OriginalTable[Cost] ) )
)

I'm actually confused as to why NOT ISBLANK( OriginalTable[Parent ID] ) doesn't work! Can't find any reference as to when BLANK() == "" and when it doesn't.