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
Arashi
Frequent Visitor

How to sum up only different values in a column

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 extensionzone
45245 43entrance
45245 55warehouse
45245 55warehouse
45245 55warehouse
45245 43entrance
45245 55warehouse
45245 43entrance

 

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

 

1 ACCEPTED SOLUTION

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

PaulOlding_0-1648638852650.png

 

View solution in original post

10 REPLIES 10
tamerj1
Super User
Super User

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

1.png

v-angzheng-msft
Community Support
Community Support

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.

  1. Sample (dummy dataset) data as text, use the table tool in the editing bar
  2. Expected output from sample data
  3. 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.

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.

 

Arashi_0-1648627979545.png

 

 

CategoryZoneStoreExtentionType of StoreUnits
Category5Warehouse1206136,6E0
Category5Warehouse2052102,06C217
Category5Warehouse2066168,07E85
Category5Warehouse2067117C174
Category5Warehouse8329116C226
Category5Warehouse83340B0
Category5Warehouse84040D0
Category4Warehouse8750118,92A639
Category4Warehouse139220B0
Category4Warehouse14009133,67D160
Category4entrance1206279,96E0
Category4entrance2052351,11C313
Category4entrance2066369,54E90
Category4entrance2067322C386
Category4entrance8329439C338
Category4entrance8334313,48B503
Category4entrance8404192,86D220
Category3entrance8750355,63A911
Category3entrance13922337,65B838
Category3entrance14009281,71D225
Category3Warehouse1206136,6E0
Category3Warehouse2052102,06C196
Category3Warehouse2066168,07E254
Category3Warehouse2067117C136
Category3Warehouse8329116C132
Category3Warehouse83340B7
Category2Warehouse84040D0
Category2Warehouse8750118,92A313
Category2Warehouse139220B0
Category2Warehouse14009133,67D52
Category2entrance1206279,96E0
Category2entrance2052351,11C489
Category2entrance2066369,54E231
Category2entrance2067322C545
Category2entrance8329439C699
Category2entrance8334313,48B1319
Category2entrance8404192,86D346
Category1entrance8750355,63A1717
Category1entrance13922337,65B1860
Category1entrance14009281,71D366

 

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

 

Thanks a lot

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

PaulOlding_0-1648638852650.png

 

It works perfectly. 

I have a lot to learn yet!

Thank you very much! 

tamerj1
Super User
Super User

Hi @Arashi 

you may try

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

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

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

Something easier (and might be faster):

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

 

 

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:

 

Arashi_0-1648504184301.png

 

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!

 

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