Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have an updating database and I need a new column that displays the sum of the existing tables as well as the new columns that appear.
As an example
Currently
1 | 2 | Name
2 | 3 | Other Task
4 | 5 | Other Other Tasks
Its going to become
1 | 2 | 3 | Name
2 | 3 | 4 | Other Task
4 | 5 | 6 |Other Other task
I want this to be an automated formula that sums every table except the text column.
Either in DAX or in M.
Thanks
Solved! Go to Solution.
Since new columns will be added all the time, you don't have a true table, but a Pivoted data set. Start by UnPiviotOtherColumns. This will take ALL Other Columns but the one(s) specified and convert them into 'Attribute' (Column Name) and 'Value' (Number Value previously in each column).
Once done, you can create a simple Table of 'Name' and SUM 'Value' to sum ALL columns other than Name even if the raw input changes. *** I couldn't get this working testing with .csv files, but I think that was because of 'type' of the new columns wasn't being defined. I hope with your real data source you might have better luck... ***
= Table.UnpivotOtherColumns(#"Changed Type", {" Name"}, "Attribute", "Value")
Proud to give back to the community!
Thank You!
Hi. Just SUM the second column + 1. In Power query you can add a custom column like [column2 name] + 1 or search in the menu the button to add values to a column. In DAX you can write Table[Column2 Name] + 1
Regards,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Happy to help!
Hey thanks for the prompt reply, The column values were just examples, the numbers are more complex than the example. The problem I am having is the number of extra columns is not specified because the client is inputting values into our CRM.
Ok, but we can't help if we don't see an example to suggest a function or measure. Can you add a sample of the data so we can understand the logic involved?
Happy to help!
1001 - Project 699 | 1000 - LOH - P2 | Name
470 11220 Anchors
1794 10201 Levelling
1794 10201 Basesq
1427 4444 GratePlatforms
736 2255 Modules
367 1161 Pinwheels
1136 990 Exteriorpanel
84 110 Crashbarrier
8 4 Aircraftcable
4 0 MP
31 0 Atticcharge
9 0 Workstation
The first N columns (1001,1000, so on) are project task totals that are required per every job (name)
I need to display the sum of all jobs. Or keep a blank table until they select that specific job which is tough because I cannot make column names part of a slicer (Making a bullet chart. Task completed data is in another table.)
Hope that makes sense.
Since new columns will be added all the time, you don't have a true table, but a Pivoted data set. Start by UnPiviotOtherColumns. This will take ALL Other Columns but the one(s) specified and convert them into 'Attribute' (Column Name) and 'Value' (Number Value previously in each column).
Once done, you can create a simple Table of 'Name' and SUM 'Value' to sum ALL columns other than Name even if the raw input changes. *** I couldn't get this working testing with .csv files, but I think that was because of 'type' of the new columns wasn't being defined. I hope with your real data source you might have better luck... ***
= Table.UnpivotOtherColumns(#"Changed Type", {" Name"}, "Attribute", "Value")
Proud to give back to the community!
Thank You!
Thanks, That seems to have worked 🙂
User | Count |
---|---|
94 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |