Forum Discussion

amilecki's avatar
amilecki
Frequent Visitor
9 years ago

SUM Using Slicer Filters

I would like to create a calculated column to determine the sum of values just like an aggregate sum from a visual (chart, matrix, etc).

 

I've created the following sample data set that mimics my real-world application.

 

The below visual shows my 3 slicers and a matrix.  I need the calculated MySum column to track the aggregated Sum(Value) data for each company.

Using the following formula, MySum does not change with my slicer selections:

MySum = CALCULATE(SUM('MyData'[Value]), ALLEXCEPT(MyData, MyData[Company]))

Adding the slicer columns to ALLEXCEPT, MySum computes the sum for unique combination of Company, Month, Slicer1, and Slicer2:

MySum = CALCULATE(SUM('MyData'[Value]), ALLEXCEPT(MyData, MyData[Company], MyData[Month], MyData[Slicer1], MyData[Slicer2]))

How can I compute MySum such that it gives the Sum by Company and updates based on my slicers?

 

Thank you.

Need Help

5 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi amilecki,

     

    Not like measures, calculate columns/tables are computed during database processing(e.g. data refresh) and then stored in the model, they do not response to user selections on the report.

     

    So it is not possible to create a calculate column/table can change dynamically with user selections on the report.

     

    In this scenario, you can create a measure to calculate the sum of values instead, then show the measure on the Table/Matrix visual with the corresponding columns from your table. The formula below to create the measure is for your reference. :smileyhappy:

    MySum = SUM('MyData'[Value])

     

    Regards

    • amilecki's avatar
      amilecki
      Frequent Visitor
      9 years ago

      v-ljerr-msftHow do I get the measure by supplier?  Let me clarify what I am attempting to do.  I am trying to create a new slicer that filters data by the Company's bucket for sum like this.

       

      Slicer

      0-4

      5-8

      9-12

      >12

       

      I thought by creating a calculated column for sum, I could also create a calculated column for bucket using a switch statement.

       

      Finally, I thought the Calculate function forces the Sum to recompute for the filters.

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Hi amilecki,

         

        While it doesn't work in that way, an alternative way to filter data by the Company's bucket for sum is to use the [MySum] measure as a visual level filter, instead of using a Slicer.

         

        For example, if you want sum from 0 to 4, then you can use [MySum] to apply a visual level filter like below. :smileyhappy:

         

         

        Regards