Ranking with slicer and two tables

[Anonymous]

Hello everybody,

I need your help again please.

I want to create a ranking based revenues, the the highest revenue on top.

I have three tables ( 1. Date, 2. Sales & 3. article overview), all tables are connect to each other.

Table Date is used as slicer.

Table Sales contains Invoiced Quantity and Sales Amount (Actual)

Table article overview contains No_ (Itemno.)

I use the following code to create the ranking:

Pos. = RANKX(ALLSELECTED(Kalender[Datum]);CALCULATE(SUM(Sales[Sales Amount (Actual)]);ALLEXCEPT('Artikelübersicht';'Artikelübersicht'[No_]));;)

The result is:

I have check almost every post I could find about creating a ranking, but noone worked.

It´s probably my fault....!

Can anybody help me to solve my problem?

Thanks a lot!!

Best regards,

freiburgc

[v-yuta-msft]

Hi freiburgc,

Which relationship have you built between 'Artikelubersicht' and 'Sales', 'Kalendar' and 'Sales'? As a workaround, you can merge table 'Artikelubersicht' with 'Sales' as a new table and try again.

Regards,

Jimmy Tao

[Anonymous]

Hello Jimmy Tao,

thanks for your answer.

The relationship between "Artikeluebersicht"  and "Sales" is based on "No_".

Relationship between "Kalender" and "Sales" is based on "Date".

I hope, that´s what you wanted to know.

Thanks & regards

Christian

[v-yuta-msft]

Hi freiburgc,

Modify your DAX formula like below and check if it can meet your requirement.

Pos. =
RANKX (
    CALCULATETABLE ( 'Sales', ALLSELECTED ( Kalender[Datum] ) ),
    CALCULATE (
        SUM ( 'Sales'[Sales Amount (Actual)] ),
        ALLEXCEPT ( 'Artikelübersicht', 'Artikelübersicht'[No_] )
    )
)

Regards,

Jimmy Tao

[Anonymous]

Hi @v-yuta-msft,

thanks for your answer.

Unfortunately your solution doesn't work. But I found a code that helped me to  optimise it than and it worked.

Ranking = IF(HASONEVALUE('Artikelübersicht'[Artikelnr. & Beschreibung]);RANKX(ALL('Artikelübersicht'[Artikelnr. & Beschreibung]);CALCULATE(Sum('Artikelposten(Item Ledger Entry)'[Sales Amount (Actual)]))))

Regards,

Christian