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

 

  • 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

     

10 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Arashi 

    you may try

    Extension Sum =
SUMX (
    SUMMARIZE ( Table, Table[Zone], "@Extention", MAX ( Table[Extention] ) ),
    [@Extention]
)
    • Arashi's avatar
      Arashi
      Frequent Visitor
      4 years ago

      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

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

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

         

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

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