Forum Discussion
dax
I have a table
col a colb
x 0
x 1
y 2
y 0
y 1
z 0
I want to create a new table
New Table
col A col B(sum of values for each col a)
x 1
y 3
z 0
can someone help me with the dax
Hi Surya1,
In your scenario, you can use SUMMARIZE() function to create a new calculated table. Please refer to following sample:
Table-2 = SUMMARIZE ( 'Table-1', 'Table-1'[col a], "col B", SUM ( 'Table-1'[col b] ) )
Thanks,
Xi Jin.
6 Replies
Do you absolutely need to do this through DAX? The query editor is very easy to use. You can get the same results by selecting
Edit Queries -> right click on your table and select Duplicate -> Go to the newly duplicated table and left click on col a and select Group By -> Group by col a and select Sum under Operation
This will give you the results that you want. If you have to do it through DAX via a calculated table, I can provide instructions for that as well, just let me know.
- Thank you for your time.Can you please help me with the dax solution
You will select New Table -> The formula will be the following:
col a = DISTINCT(Table1[col a])
You will then go to the Relationships tab and form a relationship between the col a of both tables.
Then return to the new table and select New Column -> The formula will be:
Col b := CALCULATE(SUM(Table1[col b]))
Where Table1 is the original table name.
Hi Surya1,
In your scenario, you can use SUMMARIZE() function to create a new calculated table. Please refer to following sample:
Table-2 = SUMMARIZE ( 'Table-1', 'Table-1'[col a], "col B", SUM ( 'Table-1'[col b] ) )
Thanks,
Xi Jin.
