Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Midguel
Helper I
Helper I

Create a calculated table with distinct and sum of values

Hi 🙂,

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

foto.png

to reach this final state in my new table:

Screenshot_1.png

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

2 ACCEPTED SOLUTIONS
az38
Community Champion
Community Champion

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

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

az38
Community Champion
Community Champion

@Midguel 

CALCULATE(SUM([Value]))

or more complex

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

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

7 REPLIES 7
az38
Community Champion
Community Champion

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

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

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:

Captura.PNG

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
Not applicable

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
Community Champion
Community Champion

@Midguel 

CALCULATE(SUM([Value]))

or more complex

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

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Anonymous
Not applicable

Better still... use SUMMARIZECOLUMNS.

https://dax.guide/summarizecolumns/

Best
D
az38
Community Champion
Community Champion

@Anonymous 

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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors