Summarize to create a table and add sum but with a twist!

[UrAvgWally]

Hi All,

I am trying to create a new table which adds the COWD together and treats the Project Number as a unique ID and populates the Funder field based on if it exists in another table. Example is as per below :

Source Data: Let's say Table1

Project Number	Funder	COWD
100	A	100
100	X	150
99	Z	100
99	Y	100
99	A	100

I want it to look at a table , Lets say Table2,

Funder	Department
A	IT
B	HR
C	Sales

And as A exists in Table 2, I want it to ignore the other Funder's ..

As such the output Lets say Table3 looks like this :

Project Number	Funder	COWD
100	A	250
99	A	300

Would this be possible :)? Help would be greatly appreciated!

[Adescrit · Accepted solution]

Hi @UrAvgWally ,

Try the following Dax code to create the summarized table:

Table3 =
SUMMARIZECOLUMNS (
    Table1[Project Number],
    Table1[Funder],
    FILTER ( Table1, RELATED ( Table2[Funder] ) <> BLANK () ),
    "Total COWD", SUM ( Table1[COWD] )
)

[UrAvgWally]

@Adescrit Hiya, I created a post similar to this here : https://community.powerbi.com/t5/DAX-Commands-and-Tips/Summarize-Table-with-a-twist-of-replacing-val... . Reason being, I needed the Sum of everything and not just of the values in the list. If you ever have the time to check it out, would be grateful 🙂 .