Forum Discussion
ISFILTERED Usage
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.
10 Replies
Hi karun_r
I think this calculated measure, when used on a visual gets close
Range = VAR myRev = CALCULATE(SUM('RevTable'[Revenue]), FILTER( ALLSELECTED('RevTable'),'RevTable'[Company] = MAX('RevTable'[Company]))) RETURN SWITCH( TRUE() , myRev >= 10 && myRev <= 50 , "A" , myRev > 50 && myRev <= 160 , "B" , myRev > 160 && myRev <= 300 , "C" , myRev > 300, "D" , "Other" )I have a basic PBIX file here that I think helps show my understanding of the issue.
Hi Phil_Seamark
Thanks for the reply.
I've gone through your example, and although it is an exact replica of what I've mentioned here, it is quite not what I am looking for.
For example, in a seperate table, I am showing the customer the total number of companies by the range they fall in for each fiscal year. Now, if I do the same with the example pbi that you've sent, it has the exact issue that I am facing.
You can see that even though the number of categories are 2 (B & C) it is considering only B but not C. The reason for this is still a mystery to me. Same thing is happening in my report as well.
In an attempt to fix this problem, I tried including the Category into the ALLEXCEPT function and failed miserably and ended up with lot many dummy test measures.
I think the reason for why only the range B is appearing in the table is because of the MAX function used in the Range definition, is my intuition correct ? Phil_Seamark
