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
MarkLaf
1 year agoSuper User
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
- qqqqqwwwweeerrr1 year agoSolution Sage
Thanks, this is great!! I was thinking along the same line but was not able to use window function here. New learning for me !!!