SUM Using Slicer Filters

[amilecki]

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.

[v-ljerr-msft]

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. 

MySum = SUM('MyData'[Value])

Regards

[amilecki]

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

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. 

Regards

[amilecki]

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

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. 

Reference: 

Go from Reading View to Editing View in Power BI

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

Regards