Forum Discussion
qqqqqwwwweeerrr
1 year agoSolution Sage
Grand total is incorrect while using RANKX in DAX
Hi Everyone,
Let us assume i have state, city, & sale. i want to calculate dynamically rank based on paramter. I am able to get the correct state and city based on sales but when i am looking ...
- 1 year ago
This was an interesting problem. You want the numeric range parameter to show sales of the top X states based on calculating sales of top X cities, right? Definitely involved some muddling through, but I think this what you are looking for?
Rank the sales = VAR _topXCitySales = GENERATE( ALLSELECTED( Sales_data[State] ), VAR _citySales = CALCULATETABLE( SUMMARIZECOLUMNS( Sales_data[City], "TotalSales", CALCULATE( SUM( Sales_data[Sales] ) ) ), ALLSELECTED( Sales_data[City] ) ) VAR _topXCitySales = WINDOW( 1, ABS, 'Ranks Dynamic'[Ranks Dynamic Value], ABS, _citySales, ORDERBY( [TotalSales], DESC ) ) RETURN _topXCitySales ) VAR _stateByTopXCitySales = CALCULATETABLE( SUMMARIZECOLUMNS( Sales_data[State], "TopStateSales", CALCULATE( SUM( Sales_data[Sales] ), KEEPFILTERS( _topXCitySales ) ) ), ALLSELECTED( Sales_data[State], Sales_data[City] ) ) VAR _topXStateByTopXCitySales = WINDOW( 1, ABS, 'Ranks Dynamic'[Ranks Dynamic Value], ABS, _stateByTopXCitySales, ORDERBY( [TopStateSales], DESC ) ) VAR _topXStateVals = SUMMARIZE( _topXStateByTopXCitySales, Sales_data[State] ) VAR _topXTopXSummary = FILTER( _topXCitySales, Sales_data[State] IN _topXStateVals ) RETURN CALCULATE( SUM( Sales_data[Sales] ), KEEPFILTERS( _topXTopXSummary ) )Edit: cleaned up DAX and variable names a bit, typo
Ashish_Mathur
1 year agoSuper User
Hi,
Share the download link of the PBI file.
- qqqqqwwwweeerrr1 year agoSolution Sage