creating a new table that sums values from rows of another table that have the same dateid

[darinme]

*updated for clarity*

Hi,

I want to create a table that sums values from rows of a different table where the sum is for the desired values from the same date.

Best way I can explain:

From: 

DateId	SettingName	TotalCount
20210901	Value1	50
20210901	Value2	20
20210901	Value3	111
20210902	Value1	40
20210902	Value2	10
20210901	Value3	222

Create a new table like this (where I only bring over the sum for Value 1 and Value 2):

DateId	SettingName	TotalCount
20210901	Value1Value2	70
20210902	Value1Value2	50

Note: SettingName value can be static text but can also go with concatenated.

[VahidDM]

HI @darinme 

If you want a DAX code to create a new table, try this:

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-calculated-tables

Table 2 = 
SUMMARIZE (
    'Table',
    'Table'[DateId],
    "SettingName", CONCATENATEX ( 'Table', 'Table'[SettingName], "" ),
    "TotalCount", SUM ( 'Table'[TotalCount] )
)

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[darinme]

Thanks for that and would be perfect to my posted question.  I realize that I negelcted to state there are other SettingNames values I do not want to include.  
Updating my table example where I do not want to bring over Value3 into the new table.

DateId	SettingName	TotalCount
20210901	Value1	50
20210901	Value2	20
20210901	Value3	111
20210902	Value1	40
20210902	Value2	10
20210901	Value3	222

Create a new table like this:

DateId	SettingName	TotalCount
20210901	Value1Value2	70
20210902	Value1Value2	50

[Vera_33]

Hi @darinme 

When you said a new table, it is a table you go with Power Query or DAX Calculated table, or a table visual?

You can groupby in M

Table.Group(#"Changed Type", {"DateId"}, {{"newSettingName", each Text.Combine(_[SettingName],";")}, {"newTotalCount", each List.Sum([TotalCount]), type nullable number}})

Or go with measures for a table visual

newName = CONCATENATEX('Table','Table'[SettingName],";")

newTotalCount = SUM('Table'[TotalCount])

[darinme]

Thank you.  I prefer DAX table.  I also neglected to state there are other SettingNames values I do not want to include.  
Updating my table example where I do not want to bring over Value3 into the new table.

DateId	SettingName	TotalCount
20210901	Value1	50
20210901	Value2	20
20210901	Value3	111
20210902	Value1	40
20210902	Value2	10
20210901	Value3	222

Create a new table like this:

DateId	SettingName	TotalCount
20210901	Value1Value2	70
20210902	Value1Value2	50

[VahidDM · Accepted solution]

Hi @darinme 

Try this to exclude Value3 from the new table:

Table 2 = 
SUMMARIZE (
    filter('Table','Table'[SettingName]<>"Value3"),
    'Table'[DateId],
    "SettingName", CONCATENATEX ( 'Table', 'Table'[SettingName], "" ),
    "TotalCount", SUM ( 'Table'[TotalCount] )
)

DATA :

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[darinme]

That does it!  Thanks.  I also discovered that I can do the inverse using 'IN' to only select the fields I want.  Useful when there are many values and only wnat a few.

Table 2 = 
SUMMARIZE (
    filter('Table','Table'[SettingName] IN {"Value1","Value2")},
    'Table'[DateId],
    "SettingName", CONCATENATEX ( 'Table', 'Table'[SettingName], "" ),
    "TotalCount", SUM ( 'Table'[TotalCount] )