topic Re: How to sum up only different values in a column in DAX Commands and Tips

How to sum up only different values in a column

Arashi — Sat, 26 Mar 2022 16:26:38 GMT

Hello,

I want to get a measure that sums up the total extension of the store, which should be 98 as per example below, that it sums if it has different values in column "extension".

And then if I filter either by entrance or warehouse I get the corresponding number as well:

store	extension	zone
45245	43	entrance
45245	55	warehouse
45245	55	warehouse
45245	55	warehouse
45245	43	entrance
45245	55	warehouse
45245	43	entrance

Obviously I have more that one store in my datababe and lots of different category values for each warehouse and entrance rows.

I am sure it is very simple but I don't seem to find an answer even if I researched for a while.

Thanks a lot

Re: How to sum up only different values in a column

tamerj1 — Sat, 26 Mar 2022 18:09:45 GMT

Hi Arashi

you may try

Extension Sum = SUMX ( SUMMARIZE ( Table, Table[Zone], "@Extention", MAX ( Table[Extention] ) ), [@Extention] )

Re: How to sum up only different values in a column

Arashi — Sat, 26 Mar 2022 18:26:36 GMT

Thanks for your answer. However I get stucked at "@Extention". It gives me an error when I type "

Maybe I am not understanding propperly and it is a different value I should input.

Thanks

Re: How to sum up only different values in a column

tamerj1 — Sat, 26 Mar 2022 19:01:47 GMT

Arashi

I did not try to be honest. But it could be true as SUMMARIZE is thr not the best iption to add columns. You can try

Extension Sum = SUMX ( ADDCOLUMNS ( SUMMARIZE ( Table, Table[Zone] ), "@Extention", MAX ( Table[Extention] ) ), [@Extention] )

Re: How to sum up only different values in a column

daXtreme — Sun, 27 Mar 2022 11:33:24 GMT

Something easier (and might be faster):

Extension Sum = sumx ( distinct( Table[Zone] ), calculate ( max ( Table[Extention] ) ) )

Re: How to sum up only different values in a column

Arashi — Tue, 29 Mar 2022 20:22:34 GMT

Hello,

This solution works well. However when I use this measure in a matrix with aggrupated stores, for the type of store also provides a wrong calculation:

Expected result for C stores subtotal should be the sum of the three stores total extention = 1225.

Can you help with this?

Thanks so much!

Re: How to sum up only different values in a column

v-angzheng-msft — Tue, 29 Mar 2022 02:57:08 GMT

Hi, Arashi

I can't get 1225 from the data above, would you be able to consider sharing more data and the output you expect?

It makes it easier to give you a solution.

Sample (dummy dataset) data as text, use the table tool in the editing bar
Expected output from sample data
Explanation in words of how to get from 1. to 2.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: How to sum up only different values in a column

Arashi — Wed, 30 Mar 2022 08:19:52 GMT

Hello,

I have attached in the table below the dummy set data.

The expected result is as below:

For each store I want to get the sum of the maximum value of the extension column for each type of zone.

The measure provided by daXtreme works perfectly in that sense.

However, I also want to get the aggregate of the type of store as the sum of each of the resultant value of stores based on previous measure.

Category	Zone	Store	Extention	Type of Store	Units
Category5	Warehouse	1206	136,6	E	0
Category5	Warehouse	2052	102,06	C	217
Category5	Warehouse	2066	168,07	E	85
Category5	Warehouse	2067	117	C	174
Category5	Warehouse	8329	116	C	226
Category5	Warehouse	8334	0	B	0
Category5	Warehouse	8404	0	D	0
Category4	Warehouse	8750	118,92	A	639
Category4	Warehouse	13922	0	B	0
Category4	Warehouse	14009	133,67	D	160
Category4	entrance	1206	279,96	E	0
Category4	entrance	2052	351,11	C	313
Category4	entrance	2066	369,54	E	90
Category4	entrance	2067	322	C	386
Category4	entrance	8329	439	C	338
Category4	entrance	8334	313,48	B	503
Category4	entrance	8404	192,86	D	220
Category3	entrance	8750	355,63	A	911
Category3	entrance	13922	337,65	B	838
Category3	entrance	14009	281,71	D	225
Category3	Warehouse	1206	136,6	E	0
Category3	Warehouse	2052	102,06	C	196
Category3	Warehouse	2066	168,07	E	254
Category3	Warehouse	2067	117	C	136
Category3	Warehouse	8329	116	C	132
Category3	Warehouse	8334	0	B	7
Category2	Warehouse	8404	0	D	0
Category2	Warehouse	8750	118,92	A	313
Category2	Warehouse	13922	0	B	0
Category2	Warehouse	14009	133,67	D	52
Category2	entrance	1206	279,96	E	0
Category2	entrance	2052	351,11	C	489
Category2	entrance	2066	369,54	E	231
Category2	entrance	2067	322	C	545
Category2	entrance	8329	439	C	699
Category2	entrance	8334	313,48	B	1319
Category2	entrance	8404	192,86	D	346
Category1	entrance	8750	355,63	A	1717
Category1	entrance	13922	337,65	B	1860
Category1	entrance	14009	281,71	D	366

Let me know if now it is clear or if you need any other explanation.

Thanks a lot

Re: How to sum up only different values in a column

tamerj1 — Wed, 30 Mar 2022 11:11:41 GMT

Hi Arashi
Here is a sample file with the solution https://www.dropbox.com/t/lDoepZEuJV6GCl4m

The measure is

Extension Sum = SUMX ( SUMMARIZE ('Table', 'Table'[Type of Store],'Table'[Store] ), CALCULATE ( SUMX ( SUMMARIZE ( 'Table', 'Table'[Zone], "@Extention", MAX ( 'Table'[Extention] ) ), [@Extention] ) ) )

Re: How to sum up only different values in a column

PaulOlding — Wed, 30 Mar 2022 11:15:01 GMT

Hi Arashi

Here's a measure that follows a similar pattern to others in this thread. It uses SUMMARIZE to build a table of unique Store and Extention values, then the SUMX aggregates the Extention values from there.

Max. de Extention = SUMX( SUMMARIZE('Table','Table'[Store],'Table'[Extention]), 'Table'[Extention] )

Used in a matrix you get this

Re: How to sum up only different values in a column

Arashi — Thu, 31 Mar 2022 11:48:46 GMT

It works perfectly.

I have a lot to learn yet!

Thank you very much!