Forum Discussion

Arashi's avatar
Arashi
Frequent Visitor
4 years ago
Solved

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 f...
  • PaulOlding's avatar
    PaulOlding
    4 years ago

    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

     

v-angzheng-msft's avatar
v-angzheng-msft
Icon for Community Support rankCommunity Support
4 years ago

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.

Arashi's avatar
Arashi
Frequent Visitor
4 years ago

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.

 

 

 

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

  • PaulOlding's avatar
    PaulOlding
    Icon for Solution Sage rankSolution Sage
    4 years ago

    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

     

    • Arashi's avatar
      Arashi
      Frequent Visitor
      4 years ago

      It works perfectly. 

      I have a lot to learn yet!

      Thank you very much!