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

一棵黄葛树's avatar
一棵黄葛树
Helper II
1 year ago
Rank Sales =
VAR N = SELECTEDVALUE('Ranks Dynamic'[Ranks Dynamic], 3)
VAR CurrentState = SELECTEDVALUE('Sales_data'[State])
VAR CurrentCity = SELECTEDVALUE('Sales_data'[City])
VAR AllSelectedGeo = ALLSELECTED('Sales_data'[State], 'Sales_data'[City])
RETURN
    SWITCH(
        TRUE(),
        ISINSCOPE('Sales_data'[City]),
            VAR StateFilter = TREATAS({CurrentState}, 'Sales_data'[State])
            VAR TopCities =
                CALCULATETABLE(
                    TOPN(N, VALUES('Sales_data'[City]), [Sales], DESC),
                    AllSelectedGeo,
                    StateFilter
                )
            RETURN
                IF(
                    CONTAINS(
                        TopCities,
                        'Sales_data'[City],
                        CurrentCity
                    ),
                    [Sales],
                    BLANK()
                ),
        ISINSCOPE('Sales_data'[State]),
            SUMX(
                TOPN(N,
                    CALCULATETABLE(VALUES('Sales_data'[City]), AllSelectedGeo),
                    [Sales], DESC
                ),
                [Sales]
            ),
        SUMX(
            GENERATE(
                VALUES('Sales_data'[State]),
                TOPN(N,
                    CALCULATETABLE(VALUES('Sales_data'[City]), AllSelectedGeo),
                    [Sales], DESC
                )
            ),
            [Sales]
        )
    )