Forum Discussion
dax query
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 salesSum 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])RETURNMaxCategorySales3. For CategoryMaxSalesCategory =VAR MaxSalesYear =MAX('Table'[year])VAR CategorySales =SUMMARIZE(FILTER('Table', 'Table'[year] = MaxSalesYear),'Table'[Category],"TotalSales", [Sum of Sales])VAR MaxCategorySales =MAXX(CategorySales, [TotalSales])RETURNMAXX(FILTER(CategorySales, [TotalSales] = MaxCategorySales),[Category])Result:Please consider Accepting it as the solution to help the other members find it more quickly
Hi,
Please find attached the PBI file.
Hope this helps.
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
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
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 salesSum 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])RETURNMaxCategorySales3. For CategoryMaxSalesCategory =VAR MaxSalesYear =MAX('Table'[year])VAR CategorySales =SUMMARIZE(FILTER('Table', 'Table'[year] = MaxSalesYear),'Table'[Category],"TotalSales", [Sum of Sales])VAR MaxCategorySales =MAXX(CategorySales, [TotalSales])RETURNMAXX(FILTER(CategorySales, [TotalSales] = MaxCategorySales),[Category])Result:Please consider Accepting it as the solution to help the other members find it more quickly
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 .