Forum Discussion

Matthew_Theis's avatar
Matthew_Theis
Advocate II
7 years ago
Solved

Rank Customers by Aggregated Value

Hello all,

I'm trying to rank customers by the SUM (Total POS $) and am having difficulty getting Rankx to work as I would like it to.  I have the following table:   In this case, what I would like to do would be to aggregrate Total POS $ for each pos_end_customer_name for the month.  I would like to ignore for the time being business_group and vp_area_description.  Example on Feb 1st, I would like to aggregate $ for Zyteq (197 + 32) and rank this against all other unique customers throughout the rest of the month.  I've tried the following: 

Rank C =
RANKX(
      SUMMARIZE(
            salesdashboard_pos,
            salesdashboard_pos[pos_date],
            salesdashboard_pos[pos_end_customer_name],
            "Sum Total $",
            SUM(salesdashboard_pos[Total POS $])
      ),
      MIN([Sum Total $]),
      ,
      DESC
)
But I am met with an error: "Column 'Sum Total $' cannot be found or may not be used in this expression."  
I do need this to be a calculated column so that I can perform analysis on the ranking value...ie Avg Rev for median customer +/- 500.  Any help would be greatly appreciated.

 

Thanks!

Matthew

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Matthew_Theis ,

     

    To use this formula should work.

     

    RANK C = 
RANKX (
    salesdashboard_pos,
    CALCULATE (
        MIN ( 'salesdashboard_pos'[Total POS $] ),
        ALLEXCEPT (
            salesdashboard_pos,
            'salesdashboard_pos'[pos_date],
            salesdashboard_pos[pos_end_customer_name]
        )
    ),
    ,
    DESC
)

     

1 Reply

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    Hi Matthew_Theis ,

     

    To use this formula should work.

     

    RANK C = 
RANKX (
    salesdashboard_pos,
    CALCULATE (
        MIN ( 'salesdashboard_pos'[Total POS $] ),
        ALLEXCEPT (
            salesdashboard_pos,
            'salesdashboard_pos'[pos_date],
            salesdashboard_pos[pos_end_customer_name]
        )
    ),
    ,
    DESC
)