dax query

[Arjun_reddy]

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

[Ritaf1983 · Accepted solution]

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]

category	year	sales
chairs	2021	5000			result:
tables	2021	6000			The highest value having sofa
bulbs	2022	6000			the highest value is 9200
sofa	2022	4500
chairs	2022	8000
chairs	2021	6000
chairs	2021	2800
sofa	2022	4700
bulbs	2022	2000
sofa	2021	5500
tables	2021	6000
tables	2022	3400

[Ashish_Mathur · Accepted solution]

Hi,

Please find attached the PBI file.

Hope this helps.

[Arjun_reddy]

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]

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

[Ritaf1983]

Hi @Arjun_reddy  again 🙂
Please see my solution in the linked discussion :

https://community.fabric.microsoft.com/t5/Desktop/Count-Top-N/m-p/3375418#M1123600

It has a link to the sample file.

You can use the same logic with sum instead of count.
Let me know if it is clear, if not I will give you a solution with exactly your case.

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

[Arjun_reddy]

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 · Accepted solution]

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]

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 .

[Ritaf1983]

Hi @Arjun_reddy 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523