Ranking between tables

[Anonymous]

Hello!

I would appreciate if you could help me with RANKX and its oddities.

I have one fact table and one calendar table. The fact table gathers the transactions occured hourly, depending on the type of operation, where it happened, etc. and on the year 2018. The calendar table I created goes from the 01/01/2018 00:00 until the 31/12/2018 23:00, and there I have two fields: YYYYMMDDHH and YYYYMMDD.

The point is that I need to visualize this two tables:

Current status of my visualized tables

As you can see, the first one summarize the operations hour by hour and the second day by day. In the first one there is a column (DS) that shows the total transactions for that day and the ranking it holds (well, it should show that ranking, at least). In the second is similar: for each day it shows the daily operations (DS) and the maximun hourly transactions ocurred on that day (HS) and its ranking (again, not fully working). 

All those columns are measures, coded as follows:

Daily sales

z_DS = 
SUMX(
        VALUES(Calendar[YYYYMMDD]);
        CALCULATE(
            SUM('Transactions'[Quantity]);
            ALL(Calendar[YYYYMMDDHH])
        )
    )

Hourly sales

z_HS = 
MAXX(
	KEEPFILTERS(VALUES('Calendar'[YYYYMMDDHH]));
	CALCULATE(SUM('Transactions'[Quantity]))
)

Ranking daily sales

z_RankDS = RANKX(
        ALL(Calendar[YYYYMMDD]);
        CALCULATE(
            SUM('Transactions'[Quantity]);
            ALL(Calendar[YYYYMMDDHH])
        );;DESC;Skip
    )

Ranking hourly sales

z_RankHS = RANKX(
        ALL(Calendar[YYYYMMDDHH]);
        CALCULATE(
            SUM('Transactions'[Quantity]);
            ALL(Calendar[YYYYMMDD])
        );;DESC;Skip
    )

The desired result would be something like:

YYYYMMDDHH    HS    Ranking HS    DS         Ranking DS

2018091013         102          1            1.269             7

2018090613         101          2            1.267             8

2018071213         100          3            1.261            14

---

YYYYMMDD         DS     Ranking DS      HS         Ranking HS

2018062200        1.282          1               93               22

2018062100        1.279          2               97                8

2018070600        1.278          3               97                8

---

It's worth mention that when I select a line in either table, the result for the ranks is calculated properly 

Thank you very much for your time!

[v-xicai]

Hi @Anonymous ,

You may change the ALL function to ALLSELECTED function in your formulas, see more about ALLSELECTED : https://docs.microsoft.com/en-us/dax/allselected-function-dax.

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-xicai,

Thank you for your answer, but it doesn't work. It keeps appearing a 1 :s

Best regards,

---

@v-xicai wrote:

Hi @Anonymous ,

You may change the ALL function to ALLSELECTED function in your formulas, see more about ALLSELECTED : https://docs.microsoft.com/en-us/dax/allselected-function-dax.

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

---