Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Question -
I have a drill-down bar chart that shows (Retention %).
My Logic -
I have 2 variables:
$(vRQ1B)
$(vRQ1E)
I divide by both variables to get my (Retention %).
$(=vRQ1E)/$(vRQ1B)
See Example Below -
I have Brazil selected and on the lower right had side the % of 93 is correct. Why are all the other locations showing an average when I created a drill-down?
vRQ1E:
=Count({<[Dealer Hire Date] = {'>=01/01/2016<=03/31/2016'}>}[Student ID]) - count({<[Dealer Hire Date] = {"$(= '>=' & '01/01/2016' & '<=' & '03/31/2016')"},[Dealer Term Date] = {"$(= '>=' & '01/01/2016' & '<=' & '03/31/2016')"}>} [Student ID])
vRQ1B:
=Count({<[Dealer Hire Date] = {'>=01/01/2016<=03/31/2016'}>}[Student ID])
Try this:
(Count({<[Dealer Hire Date] = {'>=01/01/2016<=12/31/2016'}>}[Student ID])
-
Count({<[Dealer Hire Date] = {"$(= '>=' & '01/01/2016' & '<=' & '12/31/2016')"},[Dealer Term Date] = {"$(= '>=' & '01/01/2016' & '<=' & '12/31/2016')"}>} [Student ID]))
/
Count({<[Dealer Hire Date] = {'>=01/01/2016<=12/31/2016'}>}[Student ID])
Have you already clicked on the green tick mark? May be it will go away once you click on it
What's the green tick mark? The Check Mark?
It works fine when I make the selection but I want to show a ranking amongst them all to show the top location and their retention.
Right the check mark. I am not sure I understand what you trying to do. Can you may be trying explaining again?
I want it to look like the above ^. However, that calculation is not showing what I want. If you look back up at the first part of this thread you'll see my calculation is showing an average across the board. Each location should have their own Retention % and not show an average of all locations. They should all have their own percent's which would then average out to 89% in this example.
I think it has something to do with dividing by variables for the drill-down. But I'm not sure.
I want the End Population / Beginning Population = Retention % (which I explained my logic above).
Does think make more sense?
Got it, would you be able to try out one of these variations:
vRQ1E/vRQ1B
or
$(vRQ1E)/$(vRQ1B)
or
$(=vRQ1E)/$(=vRQ1B)
They all are still returning the average. Below is the logic I want to represent.
Count({<[Dealer Hire Date] = {'>=01/01/2016<=12/31/2016'}>}[Student ID]) - count({<[Dealer Hire Date] = {"$(= '>=' & '01/01/2016' & '<=' & '12/31/2016')"},[Dealer Term Date] = {"$(= '>=' & '01/01/2016' & '<=' & '12/31/2016')"}>} [Student ID]) / Count({<[Dealer Hire Date] = {'>=01/01/2016<=12/31/2016'}>}[Student ID])
Maybe you'll see something that's restricting the restriction on showing per county.
Using this expression ^ this is what I get Count({<[Dealer Hire Date] = {'>=01/01/2016<=12/31/2016'}>}[Student ID]) - count({<[Dealer Hire Date] = {"$(= '>=' & '01/01/2016' & '<=' & '12/31/2016')"},[Dealer Term Date] = {"$(= '>=' & '01/01/2016' & '<=' & '12/31/2016')"}>} [Student ID]) / Count({<[Dealer Hire Date] = {'>=01/01/2016<=12/31/2016'}>}[Student ID])
Try this:
(Count({<[Dealer Hire Date] = {'>=01/01/2016<=12/31/2016'}>}[Student ID])
-
Count({<[Dealer Hire Date] = {"$(= '>=' & '01/01/2016' & '<=' & '12/31/2016')"},[Dealer Term Date] = {"$(= '>=' & '01/01/2016' & '<=' & '12/31/2016')"}>} [Student ID]))
/
Count({<[Dealer Hire Date] = {'>=01/01/2016<=12/31/2016'}>}[Student ID])
You really are very brilliant. I say it every time... but I'd be lost without your knowledge Sunny!!!
Hopefully one day I can return the favor. You're teaching me a lot!!