Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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.
https://1drv.ms/u/s!AtDlC2rep7a-oxTPbNXgwkIdeMij
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
HI @karun_r
What is the expected result you are after? Are you trying to show a differnet number fo the [Count of Company] from the visual table on the right?
Yes. Ideally, since we have selected few categories, the table on the right should show the number of companies that fall within each range value and it should take all the filters provided into context when it determines the range using the revenue. I've been struggling with figuring out how to take the "categories" into context without messing up the range assignments.
In the example pbi, with the filters as in the attached image, my ideal result would look like
Range #Companies
B 1
C 1
@Phil_Seamark, To give more context on what I'm doing, I've edited the sample pbi file and shared it here.
https://1drv.ms/u/s!AmE9ILAWJzWtgf9I75oB116mVNJi-Q
I added data for one more fiscal year and ideally what I am looking for is a comparison of number of customers in each range between fiscal years.
So my initial step was to get the range definition working for atleast 1 fiscal year then I can worry about comparing ranges from both fiscal years.
Hi @karun_r
I think I have what you are after. It involves a disconnected table with a new measure called [# Companies]
https://1drv.ms/u/s!AtDlC2rep7a-oxcAn8vXhd2Cf9m1
Hi Phil,
I've tried the approach suggested in your pbi file earlier with my original dataset and it did not work with it. The reason is the existence of multiple fiscal years and it complicates things a bit. For your reference, I've edited the sample pbi file and placed it here.
https://1drv.ms/u/s!AmE9ILAWJzWtgf9JbHAH0Oh_z3MIOw
I've added another fiscal year into the mix and created a seperate measure to calculate the number of companies for each range in the different table that you've created. You can see how it displays that few companies exist within range A even though all of them fall under B when the appropriate filters are selected.
Hi @karun_r
So how do you want the Fiscal Year to affect your expected result? Do you want to ignore it and still generate the same values?
Ideally, I want one value for each fiscal year. So, yes, the fiscal year must be considered when it assigns the range to the company. The end goal is to have a YoY comparison for each revenue range.
User | Count |
---|---|
124 | |
106 | |
99 | |
63 | |
59 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |