Create a calculated table with distinct and sum of values

[Midguel]

Hi 🙂,

I'm trying to create a calculated table from another table (the original actually has more columns) like this example:

to reach this final state in my new table:

is the sum of the value but for distinct client, so far i just have been able to create the first column using:

         table = DISTINCT(OriginalTable[Client])

create a measure will not do the work because i will be adding new columns to this modified table to be able to create a specific chart

Hope can anyone help, thank you in advance for your time!!

[az38 · Accepted solution]

Hi @Midguel 

you can use SUMMARIZE() https://docs.microsoft.com/en-us/dax/summarize-function-dax

and create new calculated table like

New Table = SUMMARIZE(OriginalTable, OriginalTable[Client], "Value", SUM(OriginalTable[Value]))

if you need more column to group use like

New Table = SUMMARIZE(OriginalTable, OriginalTable[Client], OriginalTable[OtherColumn], "Value", SUM(OriginalTable[Value]))

[az38 · Accepted solution]

Hi @Midguel 

you can use SUMMARIZE() https://docs.microsoft.com/en-us/dax/summarize-function-dax

and create new calculated table like

New Table = SUMMARIZE(OriginalTable, OriginalTable[Client], "Value", SUM(OriginalTable[Value]))

if you need more column to group use like

New Table = SUMMARIZE(OriginalTable, OriginalTable[Client], OriginalTable[OtherColumn], "Value", SUM(OriginalTable[Value]))

[Midguel]

Thank you for your reply, that solution worked but now, how can i create a new calculated column in my original table that show up like this, I am bit new using DAX:

using SumValue = SUM([Value]) only displays 420 all over the column like the last column in the image

Thank you for your time @az38 and @Anonymous 

[Anonymous]

The formula to use depends on the cardinality of your table. If the table is big, then any formula with CALCULATE will be very slow due to too many context transitions. Instead, FILTER should be used with suitable conditions.

Best
D

[az38 · Accepted solution]

@Midguel 

CALCULATE(SUM([Value]))

or more complex

CALCULATE(SUM([Value]), ALLEXCEPT(Table, Table[Value])) 

[Anonymous]

Better still... use SUMMARIZECOLUMNS.

https://dax.guide/summarizecolumns/

Best
D

[az38]

@Anonymous 

SUMMARIZECOLUMNS() is better but SUMMARIZE() is much more easier to understand and debug for newbies

[Anonymous]

SUMMARIZE is actually much more dangerous than anyone could think. Have a look at this:

https://www.sqlbi.com/articles/all-the-secrets-of-summarize/

The note about the article being obsolete should be taken with a grain of salt. SUMMARIZE is still best avoided for doing any kind of aggregations (this is what Dax Guide says even today) because this function has a very complex behavior.

Best
D