Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
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

 

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

 

 

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

 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors