Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

TopN calculated on Total

Hi

Im currently trying to use Marco Russos TopN rows and Other rows 

https://www.sqlbi.com/articles/showing-the-top-5-products-and-others-row/

This is working just fine, but it is when I add values into the columns in a matrix - the measure is now calculating top 5 for each column. 

But what I want is to show top5 in total. 

Any ideas how to solve this?

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    We can change the formula in the example to following:

     

    TopN Sales =
IF (
    ISINSCOPE ( 'Product Ranking'[Ranking group] ),
    -- Show a value only if the Ranking Group is filtered
    VAR NumOfProducts = 5
    VAR RankingGroup =
        SELECTEDVALUE ( 'Product Ranking'[Ranking group] )
    VAR TopProducts =
        TOPN (
            NumOfProducts,
            ALLSELECTED ( 'Product Ranking' ),
            CALCULATE ( [Sales Amount], ALL ( 'Store'[Store Name] ) )
        )
    RETURN
        SWITCH (
            RankingGroup,
            "Best Products", CALCULATE ( [Sales Amount], KEEPFILTERS ( TopProducts ) ),
            "Others", IF (
                NOT ISINSCOPE ( 'Product Ranking'[Ranking Name] ),
                VAR TopAmount =
                    CALCULATE ( [Sales Amount], TopProducts )
                VAR AllAmount =
                    CALCULATE ( [Sales Amount], ALLSELECTED ( 'Product Ranking' ) )
                VAR OthersAmt = AllAmount - TopAmount
                RETURN
                    OthersAmt
            )
        ),
    [Sales Amount]
)

     

    Sorry We cannot bold the difference in the Code Block. Please have a look at the third parameter of TOPN Function.

     

     


    BTW, pbix as attached.

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    We can change the formula in the example to following:

     

    TopN Sales =
IF (
    ISINSCOPE ( 'Product Ranking'[Ranking group] ),
    -- Show a value only if the Ranking Group is filtered
    VAR NumOfProducts = 5
    VAR RankingGroup =
        SELECTEDVALUE ( 'Product Ranking'[Ranking group] )
    VAR TopProducts =
        TOPN (
            NumOfProducts,
            ALLSELECTED ( 'Product Ranking' ),
            CALCULATE ( [Sales Amount], ALL ( 'Store'[Store Name] ) )
        )
    RETURN
        SWITCH (
            RankingGroup,
            "Best Products", CALCULATE ( [Sales Amount], KEEPFILTERS ( TopProducts ) ),
            "Others", IF (
                NOT ISINSCOPE ( 'Product Ranking'[Ranking Name] ),
                VAR TopAmount =
                    CALCULATE ( [Sales Amount], TopProducts )
                VAR AllAmount =
                    CALCULATE ( [Sales Amount], ALLSELECTED ( 'Product Ranking' ) )
                VAR OthersAmt = AllAmount - TopAmount
                RETURN
                    OthersAmt
            )
        ),
    [Sales Amount]
)

     

    Sorry We cannot bold the difference in the Code Block. Please have a look at the third parameter of TOPN Function.

     

     


    BTW, pbix as attached.

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi Anonymous ,


    How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    Sorry for the expired link above, pbix as attached if you still need it.

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.