Reply
avatar user
Anonymous
Not applicable

Ranking with slicer and two tables

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:

Unbenannt.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

4 REPLIES 4
v-yuta-msft
Community Support
Community Support

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

 

avatar user
Anonymous
Not applicable

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

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

 

 

avatar user
Anonymous
Not applicable

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

 

 

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)