cancel
Showing results for 
Search instead for 
Did you mean: 
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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors