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

       
Ritaf1983's avatar
Ritaf1983
Super User
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

   
Ritaf1983's avatar
Ritaf1983
Super User
3 years ago

Hi Arjun_reddy 
Glad to help 🙂
If our answers helped, please accept them as solutions, it will help to others to find them more quickly .