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

       
Arjun_reddy's avatar
Arjun_reddy
Helper I
3 years ago

I gone through the process i made the query but its not given the correct result 

my query is which is the highest value on category on max year and the value should be display 

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 .