I have a table structure like below:

Company Category FiscalYear Revenue

AdW Bikes FY17 200

AdW Cars FY17 99

AdW Acces. FY17 15

Acme Bikes FY17 299

Acme Cars FY17 150

Acme Acces. FY17 22

Now I am trying to assign a custom range label to each company based on the revenue after few filters are applied.

If the custom ranges are

A : 10-50

B: 50-160

C: 160-300

D: 300 - Above

If only Bikes are selected, Acme and Adw would fall under category C. If Bikes and Cars are selected, Acme would fall under D and Adw under C.

To achieve this behaviour, I created a measure which will aggregate the sum of the revenue of the company like below:

SumRevenue = CALCULATE(SUM(Revenue), ALLEXCEPT(RevTable, Company))

This works as expected and ignores the category division and returns the total sum revenue of the company.

I am then creating a custom column to assign label to each company based on their revenue.

But when I filter the category using a filter, even then, I still see the whole revenue of the company instead of just the category revenue. This should be expected because of the ALLEXCEPT in the measure.

To get the total revenue for one particular category through the filter, I added Category into ALLEXCEPT parameters and now the measure looks like this.

SumRevenue = CALCULATE(SUM(Revenue), ALLEXCEPT(RevTable, Company, Category))

The problem, the segment labeling(A,B,C,D) is happening for each category instead of for the entire company. This again is as expected because of the inclusion of the Category column in ALLEXCEPT.

Now my question is, how do I achieve both? When no filter is applied on the Category, the custom column with which the labels are being assigned, should conisder the entire revenue of the company instead of the category revenues. And when a filter is applied, it should take into account the revenue of that particular category and assign/change the label of the company.

Ideally, this should be the result when I use the second formula but it is still assigning labels based on the category revenues when when there is no filter on the Category.

To counter this, I tried using ISFILTERED function on Category column and based on it's outcome, I'm changing which version of the measure to use. Even this is not working and it is considering only the category revenues all the time. How do I go about fixing this one? I am sure I am missing some tiny details and any guidance would be helpful.

Forum Discussion

ISFILTERED Usage

Usage Reports

ISFILTERED multiple values

Copilot Agent Usage Report

Usage Metric report Refresh Issue

Report usage metrics