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

 

category

yearsales       
chairs20215000  result:    
tables20216000  The highest value having sofa     
bulbs20226000  the highest value is 9200     
sofa20224500       
chairs20228000       
chairs20216000       
chairs20212800       
sofa20224700       
bulbs20222000       
sofa20215500       
tables20216000       
tables20223400       
    • 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

           
    • Arjun_reddy's avatar
      Arjun_reddy
      Helper I
      3 years ago

      Hi Ashish Mathur,
      I have implemented the same way to my report, its working fine, really awesome
      Thanks a lot Ashish and Ritaf

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        3 years ago

        You are welcome.  If my previous reply helped, please mark that reply as Answer.