Forum Discussion

Alex_Hasan's avatar
Alex_Hasan
Helper II
4 years ago
Solved

Filter within TOPN

Hi, I want to write a measure that gives the top 10 parent customers' name. The measure below only shows the name of top 10 customers regardless of parent or not parent. 
 
CALCULATE(
[Total Sales],
KEEPFILTERS(TOPN(10, ALL(DimCustomer[FirstName]), [Total Sales])))
 
this is the column of DimCustomer table that represents whether a customer is a parent or not. How to include this column inside that measure? 
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Could you please check if the below measure works? 

     

     

    new measure =
CALCULATE (
    [Total Sales],
    KEEPFILTERS (
        TOPN (
            10,
            SUMMARIZE (
                FILTER (
                    SUMMARIZE (
                        ALL ( DimCustomer ),
                        DimCustomer[FirstName],
                        DimCustomer[Is parent]
                    ),
                    DimCustomer[Is parent] = "Parent"
                ),
                DimCustomer[FirstName]
            ),
            [Total Sales]
        )
    )
)

     

  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    After I see your pbix file, I am confused and I am not sure what you want to visualize.

    Do you want to rank total sales by First name? For example, I see that there are so many "Aaron"s and some of them are parent and some of them are Not parent.

    I think you can put a correct column in the visualization to check top sales.

    I did not check whether the measures still work, but I inside the TOPN function in each measure, including TOP sales measure, please select the correct column, or you can just select the whole table if there are other columns involved such as sorting purpose, for instance.

    Thanks.

    Thanks.

10 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    Could you please check if the below measure works? 

     

     

    new measure =
CALCULATE (
    [Total Sales],
    KEEPFILTERS (
        TOPN (
            10,
            SUMMARIZE (
                FILTER (
                    SUMMARIZE (
                        ALL ( DimCustomer ),
                        DimCustomer[FirstName],
                        DimCustomer[Is parent]
                    ),
                    DimCustomer[Is parent] = "Parent"
                ),
                DimCustomer[FirstName]
            ),
            [Total Sales]
        )
    )
)

     

    • Alex_Hasan's avatar
      Alex_Hasan
      Helper II
      4 years ago

      Hi Jihwan_Kim , I've found a problem in the measure. It's actually not showing the top 10 parents or top 10 not parents, rather top 10 customers. I've altered the code to see the effect but both are the same. On the other hand, Summerize function has a bug and Alberto Ferrari of SQLBI told to avoid this function.

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        4 years ago

        Hi,

        Thank you for your message, and please share your sample pbix file's link together with how the expected outcome looks like, and then I can try to look into it to come up with a more accurate solution.

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Alex_Hasan 

    For some reason I've never succeeded to answer any of your questions before 😅

    Maybe this time. Please try

    top 10 parent customers' name =
CALCULATE (
    [Total Sales],
    KEEPFILTERS (
        TOPN (
            10,
            FILTER (
                ALL ( DimCustomer[FirstName] ),
                CALCULATE ( VALUES ( DimCustomer[Is parent] ) ) = "Parent"
            ),
            [Total Sales]
        )
    )
)
    • Alex_Hasan's avatar
      Alex_Hasan
      Helper II
      4 years ago

      tamerj1  ha ha ha..... Most probably I couldn't make you understand my problem. But your code is not working this time. Maybe you forgot to add something in the measure. Here Jihwan Kim provided the solution and it worked.