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 into grand total it is not correct. 

 

Here is my measure to filter state and city: 

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
        )

RETURN
        IF(results <= 'Ranks Dynamic'[Ranks Dynamic Value],SUM(Sales_data[Sales]))
Is there a way to modify this and get correct grand total & sub total: if am doing only by state using other measure i am able to achive but the moment i try city it is not correct 
 

Thanks in Advance

Regards

 
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

7 Replies

  • 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. 

  • MarkLaf's avatar
    MarkLaf
    Super User
    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

    • qqqqqwwwweeerrr's avatar
      qqqqqwwwweeerrr
      Solution Sage
      1 year ago

      Thanks, this is great!! I was thinking along the same line but was not able to use window function here. New learning for me !!!

  • 一棵黄葛树'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]
            )
        )