topic Re: Create a calculated table with distinct and sum of values in DAX Commands and Tips

Create a calculated table with distinct and sum of values

Anonymous — Fri, 06 Mar 2020 06:26:59 GMT

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!!

Re: Create a calculated table with distinct and sum of values

az38 — Fri, 06 Mar 2020 07:01:05 GMT

Hi Anonymous

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

Re: Create a calculated table with distinct and sum of values

Anonymous — Fri, 06 Mar 2020 09:59:55 GMT

Better still... use SUMMARIZECOLUMNS.

https://dax.guide/summarizecolumns/

Best
D

Re: Create a calculated table with distinct and sum of values

az38 — Fri, 06 Mar 2020 16:52:26 GMT

Anonymous

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

Re: Create a calculated table with distinct and sum of values

Anonymous — Fri, 06 Mar 2020 17:28:06 GMT

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

Re: Create a calculated table with distinct and sum of values

az38 — Fri, 06 Mar 2020 17:33:26 GMT

Anonymous

CALCULATE(SUM([Value]))

or more complex

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

Re: Create a calculated table with distinct and sum of values

Anonymous — Fri, 06 Mar 2020 17:49:22 GMT

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

Re: Create a calculated table with distinct and sum of values

Anonymous — Fri, 06 Mar 2020 17:52:28 GMT

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