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