Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Please look at the screenshot below.
I just sorted on Funds from Highest to lowest and since CVS HEALTH has the highest Funds overall, the Rank for all CVS from 'Giver' column should be 1.
Then all rows with 'Giver' - ANHEUSER-BUSCH CO/INBEV should be 2, COMCAST A should be 3 and so on...
So it sounds like you want max() instead of sum()?
Using the following as a dimension:
=Aggr(Rank(aggr(max(Funds),Cat), 1, 1),Cat)
Results in this:
Cat | Company | Date | Giver | Funds | max(total <Cat> Funds) | =Aggr(Rank(aggr(max(Funds),Cat), 1, 1),Cat) |
CVS | A | 3/6/2018 | CVS HEALTH A | 9000000000 | 9000000000 | 1 |
CVS | A | 3/6/2018 | CVS HEALTH A | 8000000000 | 9000000000 | 1 |
CVS | A | 3/6/2018 | CVS HEALTH A | 6000000000 | 9000000000 | 1 |
CVS | A | 3/6/2018 | CVS HEALTH A | 5000000000 | 9000000000 | 1 |
CVS | A | 3/6/2018 | CVS HEALTH A | 3000000000 | 9000000000 | 1 |
CVS | A | 3/6/2018 | CVS HEALTH A | 2000000000 | 9000000000 | 1 |
CVS | A | 3/6/2018 | CVS HEALTH A | 1000000000 | 9000000000 | 1 |
ABIBB | A | 3/20/2018 | ANHEUSER-BUSCH INBEV WOR | 2500000000 | 4250000000 | 2 |
ABIBB | A | 3/20/2018 | ANHEUSER-BUSCH INBEV WOR | 1500000000 | 4250000000 | 2 |
ABIBB | A | 3/20/2018 | ANHEUSER-BUSCH INBEV WOR | 500000000 | 4250000000 | 2 |
ABIBB | A | 1/10/2019 | ANHEUSER-BUSCH CO/INBEV | 4250000000 | 4250000000 | 2 |
ABIBB | A | 1/10/2019 | ANHEUSER-BUSCH CO/INBEV | 4000000000 | 4250000000 | 2 |
ABIBB | A | 1/10/2019 | ANHEUSER-BUSCH CO/INBEV | 2500000000 | 4250000000 | 2 |
ABIBB | A | 1/10/2019 | ANHEUSER-BUSCH CO/INBEV | 2000000000 | 4250000000 | 2 |
ABIBB | A | 1/10/2019 | ANHEUSER-BUSCH CO/INBEV | 750000000 | 4250000000 | 2 |
CMCSA | A | 2/1/2018 | COMCAST A | 1200000000 | 4000000000 | 3 |
CMCSA | A | 2/1/2018 | COMCAST A | 1000000000 | 4000000000 | 3 |
CMCSA | A | 10/2/2018 | COMCAST A | 4000000000 | 4000000000 | 3 |
CMCSA | A | 10/2/2018 | COMCAST A | 3000000000 | 4000000000 | 3 |
CMCSA | A | 10/2/2018 | COMCAST A | 2500000000 | 4000000000 | 3 |
CMCSA | A | 10/2/2018 | COMCAST A | 2000000000 | 4000000000 | 3 |
CMCSA | A | 10/2/2018 | COMCAST A | 1750000000 | 4000000000 | 3 |
CMCSA | A | 10/2/2018 | COMCAST A | 1500000000 | 4000000000 | 3 |
CMCSA | A | 10/2/2018 | COMCAST A | 1250000000 | 4000000000 | 3 |
CMCSA | A | 10/2/2018 | COMCAST A | 1000000000 | 4000000000 | 3 |
CMCSA | A | 10/2/2018 | COMCAST A | 500000000 | 4000000000 | 3 |
CI | A | 9/6/2018 | CIGNA A | 3800000000 | 3800000000 | 4 |
CI | A | 9/6/2018 | CIGNA A | 3100000000 | 3800000000 | 4 |
CI | A | 9/6/2018 | CIGNA A | 3000000000 | 3800000000 | 4 |
CI | A | 9/6/2018 | CIGNA A | 2200000000 | 3800000000 | 4 |
CI | A | 9/6/2018 | CIGNA A | 1750000000 | 3800000000 | 4 |
CI | A | 9/6/2018 | CIGNA A | 1250000000 | 3800000000 | 4 |
CI | A | 9/6/2018 | CIGNA A | 1000000000 | 3800000000 | 4 |
CI | A | 9/6/2018 | CIGNA A | 700000000 | 3800000000 | 4 |
Also attaching the example file with this change.
The following works as a dimension:
It's working properly based on the sum of Funds per Cat (I've updated the chart in your example file to contain the sum of Funds by Cat as an expression).
Do you maybe want it grouped by Giver instead of Cat? I've added a second chart that does that.
@Nicole-Smith Thanks you so much. Let me check this one
Please look at the attached Excel Source file. Here, you can clearly see that the Highest to lowest Funds with respect to Cat, the order is different. I have added a Rank column manually to help you understand how my rankings should look like.
1. How do I get them to show same rank if the Funds value is same instead of multiple numbers like below?
2. This is how my Ranks should look like, if you look at the Excel file sorted based on Funds from highest to lowest.
If grouping by Cat, JPM totals to more than CVS (as seen in the first chart in my previous example file--second to last column). I'm not sure what your Excel file is doing to ignore JPM. From the totals on that first chart, I can see that the ranking function that I wrote is working properly.
If you want it to show the values without the dashes, you can change the extra parameters of the Rank function to fit your needs. I usually go with 1 and 1 but other options can be found here. For example:
=Aggr(Rank(aggr(Sum(Funds),Cat), 1, 1),Cat)
If you look at the Excel file attached or even the screenshot, JPM is not the highest Funds, it is the CVS and then ABIBB and CMCSA and so on....
You're right--the sum I have isn't working properly. If I can figure out why, I can fix this rank function...