Grand total is incorrect while using RANKX in DAX

[qqqqqwwwweeerrr]

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

 

[一棵黄葛树]

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]

        )

    )

[MarkLaf · Accepted solution]

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]

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

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[qqqqqwwwweeerrr]

Hi @Ashish_Mathur , 

Here it is

[Anonymous]

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]

Hi @Anonymous , 

Thanks for your response. I am still getting same output 

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