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 ca...
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