Forum Discussion

330SMG's avatar
330SMG
Frequent Visitor
4 years ago
Solved

Issue with Rankx measure in Table

Hi

I have the following Measure created.  

Top5 Customers = CALCULATE([Total Orders],
FILTER(VALUES(DimCustomer[FirstName]),
IF(RANKX(ALL(DimCustomer[FirstName]),[Total Orders],,DESC)<=5,[Total Orders],BLANK())))
Total Orders is another measue I created Summing the orders
When I place this in a table for visualization I am unable to see the names of the top 5 order customers, I just get a total.  Where is this breaking?
Need Help
  • SpartaBI's avatar
    SpartaBI
    4 years ago

    330SMG this is the measure for top 5 first names:

     

     

    Top5 First Names = 
    CALCULATE(
        [Total Orders],
        FILTER(
            VALUES(DimCustomer[FirstName]),
            IF(RANKX(ALL(DimCustomer[FirstName]),[Total Orders],,DESC)<=5,[Total Orders],BLANK())
        )
    )

     

     


    After you created the measure you need to make sure to put the right column in the visual (In this case. [FirstName], in the previous case [CustomerKey]). That is why in both cases I get the right result with the same data you have.

     


    Showcase Report – Contoso By SpartaBI


          

14 Replies

  • SpartaBI's avatar
    SpartaBI
    Icon for Community Champion rankCommunity Champion
    4 years ago

    330SMG I tested your measure on my sample data and it's working:

    Also created a 2nd version, so you can try it, but if the 1st one didn't work then good chance this won't work neither:

    Top5 Customers 2 =
    CALCULATE(
    [Sales Amount],
    KEEPFILTERS( TOPN(5, ALLSELECTED(Customer[CustomerKey]), [Sales Amount] ) )
    )
    Just replace my column names with yours. 
    1. Is [First Name] a unique key in your customer table?
    2. Maybe you don't have a proper relatioship between dim customers and the fact?



    • 330SMG's avatar
      330SMG
      Frequent Visitor
      4 years ago

      So I changed it to the column that is the join between the tables Customer Key and same thing, it just shows the total in the visulization not the top 5.  

      Oh and I tried your TopN Dax,  Same thing.  My joins all apear correct, but obviously they art not.  I am realitively new to BI and am really stumped by this.  I need to understand the mechanism on why it is not creating individual entries.  I have worked with SQL and Excel for a long time and I am a little lost on this error.