Forum Discussion

qqqqqwwwweeerrr's avatar
qqqqqwwwweeerrr
Solution Sage
1 year ago
Solved

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 ...
Need Help
  • MarkLaf's avatar
    MarkLaf
    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

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi qqqqqwwwweeerrr 

 

Pls try:

 

Rank the sales =
VAR rankcity = RANKX(ALLSELECTED(Sales_data[City]), [total sales], , DESC, Dense)
VAR rankstate = RANKX(ALLSELECTED(Sales_data[State]), [total sales], , DESC, Dense)

VAR results =
    SWITCH(
        TRUE(),
        ISINSCOPE(Sales_data[City]), rankcity,
        ISINSCOPE(Sales_data[State]), rankstate
    )

VAR totalSales =
    SUMX(
        FILTER(
            ALLSELECTED(Sales_data),
            SWITCH(
                TRUE(),
                ISINSCOPE(Sales_data[City]), RANKX(ALLSELECTED(Sales_data[City]), [total sales], , DESC, Dense) <= 'Ranks Dynamic'[Ranks Dynamic Value],
                ISINSCOPE(Sales_data[State]), RANKX(ALLSELECTED(Sales_data[State]), [total sales], , DESC, Dense) <= 'Ranks Dynamic'[Ranks Dynamic Value],
                TRUE(), FALSE()
            )
        ),
        Sales_data[Sales]
    )

RETURN
    IF(
        HASONEVALUE(Sales_data[City]) || HASONEVALUE(Sales_data[State]),
        IF(results <= 'Ranks Dynamic'[Ranks Dynamic Value], SUM(Sales_data[Sales])),
        totalSales
    )

 

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • qqqqqwwwweeerrr's avatar
    qqqqqwwwweeerrr
    Solution Sage
    1 year ago

    Hi Anonymous , 

     

    Thanks for your response. I am still getting same output 

    I have attchaed the PBI file for your refernece in question.