Rank x.... Again

[Craigc3814]

Im reading tons of forums about RankX it seems amazing but every single forum I read seems to be a unique issues. Here is mine

I have a column called Asset Address then 12 months of data for each asset.

I wanted to rank my Base Metered Demand TOD column that is a decimal number based on the Asset Address column

Meaning I want rankings by asset address. I do not want other address numbers impacting the rankings of another address.

I am using this formula

=RANKX(ALL(RTS[Asset Address]),RTS[Base Metered Demand TOD])

It only returns 1's

If I switch to this as many forums say it still returns 1's

=RANKX(ALL(RTS[Asset Address]),CALCULATE(sum(RTS[Base Metered Demand TOD])))

 

[VBA Geek]

Hi Craig, could you post a sample of your table

 

[Craigc3814]

Hey VBA Geek, its the same data set you have been helping me with. I started a new thread because I changed my approach.

It has 12 months of data per asset

Asset Address is Column 1, Usage Month is column 2 Base Metered Demand is column 3 (Its basically how much energy an asset used). I am just trying to rank the 12 months of Base Metered Demand per asset. There are 5 total assets in the data so about 60 rows worth of data.

I want to look at column 1 and rank the 12 months worth of data in column 3. I do not want Asset #2 rankings in asset #1 data if that makes sense.

I am also following your blog now, thanks for all the help.

 

[VBA Geek]

You can do it like this:



Ranking =<br><span class="Keyword" style="color:#0070FF">IF</span><span class="Parenthesis" style="color:#969696"> (</span><br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">HASONEVALUE</span><span class="Parenthesis" style="color:#969696"> (</span> Data[Month] <span class="Parenthesis" style="color:#969696">)</span>,<br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">RANKX</span><span class="Parenthesis" style="color:#969696"> (</span> <span class="Keyword" style="color:#0070FF">ALLSELECTED</span><span class="Parenthesis" style="color:#969696"> (</span> Data[Month] <span class="Parenthesis" style="color:#969696">)</span>, <span class="Keyword" style="color:#0070FF">CALCULATE</span><span class="Parenthesis" style="color:#969696"> (</span> <span class="Keyword" style="color:#0070FF">SUM</span><span class="Parenthesis" style="color:#969696"> (</span> Data[Rate] <span class="Parenthesis" style="color:#969696">)</span> <span class="Parenthesis" style="color:#969696">)</span> <span class="Parenthesis" style="color:#969696">)</span><br><span class="Parenthesis" style="color:#969696">)</span><br>

thanks for the blog! need to start posting more frequently

 

[Craigc3814]

This worked perfectly in a pivot table. Is there a way I can just make it a calculated column in the data model?

 

[VBA Geek]

This worked perfectly in a pivot table. Is there a way I can just make it a calculated column in the data model?

yes, like this:

=<br><span class="Keyword" style="color:#0070FF">CALCULATE</span><span class="Parenthesis" style="color:#969696"> (</span><br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">COUNTROWS</span><span class="Parenthesis" style="color:#969696"> (</span> Data <span class="Parenthesis" style="color:#969696">)</span>,<br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">ALLEXCEPT</span><span class="Parenthesis" style="color:#969696"> (</span> Data, Data[Acct #] <span class="Parenthesis" style="color:#969696">)</span>,<br>    Data[Rate] > <span class="Keyword" style="color:#0070FF">EARLIER</span><span class="Parenthesis" style="color:#969696"> (</span> Data[Rate] <span class="Parenthesis" style="color:#969696">)</span><br><span class="Parenthesis" style="color:#969696">)</span><br>    + <span class="Number" style="color:#EE7F18">1</span><br>

 

[Craigc3814]

I honestly don't know how to thank you, if you can just me know how.

I have one final question and it is a MONSTER of a question, I am not even sure I can explain it very well.

Lets Say August was my highest ranked month for a given asset. Is there a way I can then filter out ALL months that come before August?

In my final calculation those months become irrelevant because the rate is set by the most recent highest month.

So an easy 1 is Asset 1 the highest month is August, I just want to keep the data in my pivot table for August, and September at this point (October invoice not received yet)

But then on my second asset February is the current highest month, so January, December, and November are irrelevant.

Essentially I am trying to calculate out the future charges for an asset.

Once the max usage has hit 12 months old the charges for that month FINALLY fall off.

So if Max usage is set in April I have 6 months of that usage to pay

If the next highest month is May by the time April falls off I only have to pay 1 month of Mays Max

If the following highest month is June, by the time May falls off I have more months of June to pay.

I am sure this makes zero sense, and I may be reaching but you seem to really understand this stuff.

THANK YOU SO MUCH!!!!!

 

[VBA Geek]

I am a bit lost, sorry. Could you show me with a practical example, maybe using the results of my previous post?

I honestly don't know how to thank you, if you can just me know how.

I have one final question and it is a MONSTER of a question, I am not even sure I can explain it very well.

Lets Say August was my highest ranked month for a given asset. Is there a way I can then filter out ALL months that come before August?

In my final calculation those months become irrelevant because the rate is set by the most recent highest month.

So an easy 1 is Asset 1 the highest month is August, I just want to keep the data in my pivot table for August, and September at this point (October invoice not received yet)

But then on my second asset February is the current highest month, so January, December, and November are irrelevant.

Essentially I am trying to calculate out the future charges for an asset.

Once the max usage has hit 12 months old the charges for that month FINALLY fall off.

So if Max usage is set in April I have 6 months of that usage to pay

If the next highest month is May by the time April falls off I only have to pay 1 month of Mays Max

If the following highest month is June, by the time May falls off I have more months of June to pay.

I am sure this makes zero sense, and I may be reaching but you seem to really understand this stuff.

THANK YOU SO MUCH!!!!!

 

[Craigc3814]

I have decided I am going to work 1 issue at a time and learn enough along the way that I can ask this question in a sensible way. Thank you so much for your help, as mentioned I have your forum added into my favorites and will be checking it regularly.