topic Re: creating a new table that sums values from rows of another table that have the same dateid in DAX Commands and Tips

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

darinme — Tue, 30 Nov 2021 01:40:09 GMT

*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.

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

Anonymous — Tue, 30 Nov 2021 00:42:22 GMT

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

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

VahidDM — Tue, 30 Nov 2021 01:22:16 GMT

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/

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

darinme — Tue, 30 Nov 2021 01:35:11 GMT

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

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

darinme — Tue, 30 Nov 2021 01:37:33 GMT

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

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

VahidDM — Tue, 30 Nov 2021 01:59:01 GMT

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/

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

darinme — Tue, 30 Nov 2021 18:34:23 GMT

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