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 ...
Need Help
v-ljerr-msft's avatar
v-ljerr-msft
Microsoft 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
    Microsoft 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

    • amilecki's avatar
      amilecki
      Frequent Visitor
      9 years ago

      v-ljerr-msftThis works for me in the desktop version although breaks up the useability.  When I publish to the web, will other users be able to modify the filter values?

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

        Hi amilecki,

        There are two modes for interacting with reports: Reading View and Editing View.  And the filtering capabilities available to you depend on which mode you're in.

        • In Editing View, you can add report, page, and visual filters. When you save the report, the filters are saved with it. People looking at the report in Reading View can interact with the filters you added, but not save their changes.

        • In Reading View, you can interact with any report, page and visual filters that already exist in the report, but you won't be able to save your filter changes.


        Other users can modify the filter values in Editing View on Power BI service. :smileyhappy:

         

        Reference: 

        Go from Reading View to Editing View in Power BI

        Add a filter to a Power BI report (in Editing view)

         

        Regards