Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
*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.
Solved! Go to 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/
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/
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 |
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])
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 |
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/
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] )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |