Forum Discussion

Arjun_reddy's avatar
Arjun_reddy
Helper I
3 years ago
Solved

dax query

Hi Community team,

 

I have category, year, totalsales fields in sales table
I want to write a dax query - which category is having the highest value and what is the max value on total sales on category in max year
it should be return only which is the max value in category

please help me on my query

Thanks,

Arjun reddy

Need Help
  • Ritaf1983's avatar
    Ritaf1983
    3 years ago

    Hi Arjun_reddy 
    Sorry, I didn't see that you need it for the last year.
    In this case the daxes are :
    1. Basic sum of sales 

    Sum of Sales = sum('Table'[sales])
    2. Maximum sum of sales by category and last year :
    Max Sales by Category and Last year =
    VAR MaxSalesYear =
        MAX('Table'[year])

    VAR CategorySales =
        SUMMARIZE(
            FILTER('Table', 'Table'[year] = MaxSalesYear),
            'Table'[Category],
            "TotalSales", [Sum of Sales]
        )

    VAR MaxCategorySales =
        MAXX(CategorySales, [TotalSales])

    RETURN
      MaxCategorySales
     
    3. For Category 
    MaxSalesCategory =
    VAR MaxSalesYear =
        MAX('Table'[year])

    VAR CategorySales =
        SUMMARIZE(
            FILTER('Table', 'Table'[year] = MaxSalesYear),
            'Table'[Category],
            "TotalSales", [Sum of Sales]
        )

    VAR MaxCategorySales =
        MAXX(CategorySales, [TotalSales])

    RETURN
        MAXX(
            FILTER(CategorySales, [TotalSales] = MaxCategorySales),
            [Category])
     
    Result:

    Link to a sample file 

    Please consider Accepting it as the solution to help the other members find it more quickly

       

9 Replies