Forum Discussion

memoalkatib1's avatar
memoalkatib1
Frequent Visitor
3 years ago

RANKX

Hi ,

 

I am trying to create a RANK for product category based amongst all prodcut categories and display it on one KPI CARD( so it would just show the RANK, based on Total amount of sales desc.

The Rank for specific Category will be displayed based on the filtered retailer. so for example. If you select Walmart, the specific category named = " Clothes" ranks 5th or 6th or 7th amongst the overall sales of all catgories. you can either specify the category name explicitally in the measure, or have a slicer/ filter on the CARD visual. Either way is fine. 
RANKX

 


I wrote the measure and It works absolutely fine when I add a table that contains Retailer, Product Category and Rank. But IF I only soon as I add a Card visual to only show the rank number, and slice it by the Category Name= "Shirt", It will throw off the number by 1 rank at least. Does anyone have any idea on why that might be ?

Here is my 2 DAX measures:

Total_Sales=
VAR VAL = CALCULATE(SUM(Sales[Sale_Amount]),FILTER(ProductCategory, ProductCategory [Name<>"MISC"),FILTER(ProductCategory, ProductCategory [Name] <>BLANK()), FILTER('Calendar','Calendar'[Year]=[Max Year]))

return VAL




Rank_Sales

VAR Val =
    RANKX ( FILTER(FILTER(ProductCategory, ProductCategory [Name]<>"MISC"),FILTER(ProductCategory, ProductCategory [Name] <>BLANK())  , [Total_Sales],,DESC,Dense)
RETURN

VAL
















RANKx  RANKX measure for each group RANKX

"MEASURE"
DAX RANKX
RANKX
RANKX issue

2 Replies

  • EricVieira's avatar
    EricVieira
    Regular Visitor
    3 years ago

    Categoria classificada =
    VAR TotalSales = CALCULATE(SUM(SalesTable[Sales]))
    RETURN
    RANKX(
    ALL(ProductTable[Category]),
    CALCULATE(SUM(SalesTable[Sales])),
    ,
    DESC,
    Dense
    )

    Assuming you have a sales table named 'SalesTable' with a column 'Sales' representing the sales value, and a product table named 'ProductTable' with a column 'Category' representing the product category.

    The above code calculates the total sales for all products using the CALCULATE function with the SUM function. Then we use the RANKX function to rank each product category based on total sales, using the CALCULATE function with the SUM function to calculate sales for each category. The ALL function removes all product category filters, so that all categories are ranked based on total sales.

     

    • memoalkatib1's avatar
      memoalkatib1
      Frequent Visitor
      3 years ago

      Thank you, This is exactly what I did on my measure. Again, It works fine on a table visual, but as soon as you add a signle KPI card, then add a visual level filter to filter by Category= "Clothes", then the rank is not accurate.