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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.