topic Summarize to create a table and add sum but with a twist! in DAX Commands and Tips

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

UrAvgWally — Thu, 18 Aug 2022 14:29:32 GMT

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!

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

Adescrit — Thu, 18 Aug 2022 16:47:33 GMT

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] ) )

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

UrAvgWally — Thu, 01 Sep 2022 12:38:31 GMT

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-values-to-a-list/m-p/2740727#M84221 . 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 🙂 .