Rankx an Aggregated Fieldin a calculated column while ignoring others.

Question

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.&nbsp; I have the following table:&nbsp;&nbsp;In this case, what I would like to do would be to aggregrate Total POS $ for each pos_end_customer_name for the month.&nbsp; I would like to ignore for the time being business_group and vp_area_description.&nbsp; 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.&nbsp; I've tried the following:&nbsp;Rank C =RANKX(&nbsp; &nbsp; &nbsp; SUMMARIZE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; salesdashboard_pos,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; salesdashboard_pos[pos_date],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; salesdashboard_pos[pos_end_customer_name],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "Sum Total $",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUM(salesdashboard_pos[Total POS $])&nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; MIN([Sum Total $]),&nbsp; &nbsp; &nbsp; ,&nbsp; &nbsp; &nbsp; DESC)But I am met with an error: "Column 'Sum Total $' cannot be found or may not be used in this expression."&nbsp;&nbsp;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.&nbsp; Any help would be greatly appreciated.&nbsp;Thanks!Matthew

alb · Answer

Hi Matthew_Theis&nbsp;
Can you please share&nbsp; the table in the screen capture in text-tabular format so that it can be readily copied? Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).

v-cherch-msft · Answer

Hi&nbsp;Matthew_Theis&nbsp;
You may try below calculated column:
Column =
RANKX (
    FILTER (
        salesdashboard_pos,
        salesdashboard_pos[pos_date] = EARLIER ( salesdashboard_pos[pos_date] )
    ),
    CALCULATE (
        SUM ( salesdashboard_pos[Total POS $] ),
        ALLEXCEPT (
            salesdashboard_pos,
            salesdashboard_pos[pos_end_customer_name],
            salesdashboard_pos[pos_date]
        )
    ),
    ,
    DESC
)

Regards,

matthew_theis · Answer

Here you are&nbsp;AlB&nbsp; &nbsp;Thanks in advance.pos_datebusiness_groupvp_area_descriptionpos_end_customer_nameTotal POS $Total POS QRank CRank Customers by MonthRank Customers by Month by BGIndexFriday, February 1, 2019CBGEUROPEZYTEQ TECHNOLOGIES70632#ERROR26519126941823291Friday, February 1, 2019CBGEUROPEZYTEQ1972160#ERROR1918091431823289Friday, February 1, 2019FEBGEUROPEZYTEQ32210#ERROR3268081701823290Friday, March 1, 2019CBGAMERICASZYNEX MEDICAL INC17400#ERROR23955125771856519Friday, February 1, 2019CBGAMERICASZYNEX MEDICAL56320000#ERROR1241459201823287Friday, March 1, 2019CBGAMERICASZYNEX MEDICAL15370#ERROR24527129151856518

matthew_theis · Answer

Hello&nbsp;v-cherch-msft&nbsp; and thank you for the input.&nbsp; The calculation that you provided gets us very close to the desired solution.&nbsp; What's happening is that we are ranking with a Skip by sum(Total POS $).&nbsp; If we change to Dense, that will solve the problem towards the top end of the list, where sum(Total POS $) is unique for each customer.&nbsp; We run into a problem towards the bottom of the order where many distinct customers share the same value of sum(Total POS $).&nbsp; I would like to see a ranking for distinct customers...which is where a Skip would make sense, but that doesn't work at the top of the list (Ex. Zyteq Technologies should have a rank of 3).&nbsp;&nbsp;I kept at it with this...Total Customer Sales per Month = VARCurrentDate = salesdashboard_pos[pos_date]VARCurrentCustomer = salesdashboard_pos[pos_end_customer_name]ReturnCALCULATE(SUMX(FILTER(ALL(salesdashboard_pos),salesdashboard_pos[pos_date]=CurrentDate &amp;&amp;salesdashboard_pos[pos_end_customer_name]=CurrentCustomer),salesdashboard_pos[Total POS $]))and then I ranked that Dense...This looks alright at the top, but lower you begin to see the issue.&nbsp; 1. Kelei has a counterpart somewhere else with a Total POS $ of 1 and Rank per Month of 4786.&nbsp; It's at this point, where multiple distinct customers share the same Total Customer Sales per Month (also Rank) where we would want to skip the rank.&nbsp; Any ideas on how to solve this issue?&nbsp;Thanks!Matthew

v-cherch-msft · Answer

Hi&nbsp;Matthew_Theis&nbsp;
Could you share some simplified sample data which&nbsp;could reproduce&nbsp;your scenario and your desired output?
Regards,

Forum Discussion

Rankx an Aggregated Fieldin a calculated column while ignoring others.

5 Replies

Related content

Ignore relationship in CALCULATE

Dax calculate total amount ignoring filter

Aggregate XIRR Calculation Help

Calculate previous month aggregate amount

Data Aggregation issue

pos_date	business_group	vp_area_description	pos_end_customer_name	Total POS $	Total POS Q	Rank C	Rank Customers by Month	Rank Customers by Month by BG	Index
Friday, February 1, 2019	CBG	EUROPE	ZYTEQ TECHNOLOGIES	70	632	#ERROR	26519	12694	1823291
Friday, February 1, 2019	CBG	EUROPE	ZYTEQ	197	2160	#ERROR	19180	9143	1823289
Friday, February 1, 2019	FEBG	EUROPE	ZYTEQ	32	210	#ERROR	32680	8170	1823290
Friday, March 1, 2019	CBG	AMERICAS	ZYNEX MEDICAL INC	17	400	#ERROR	23955	12577	1856519
Friday, February 1, 2019	CBG	AMERICAS	ZYNEX MEDICAL	563	20000	#ERROR	12414	5920	1823287
Friday, March 1, 2019	CBG	AMERICAS	ZYNEX MEDICAL	15	370	#ERROR	24527	12915	1856518