Rank Customers by Aggregated Value

[Matthew_Theis]

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

[v-frfei-msft · Accepted solution]

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
)