PowerPivot DAX - Creating column based on most common value in another column

Question

I have a table in my Power Pivot model that includes customer IDs, account IDs, and sales for a bunch of transactions. The problem is that some account IDs are missing.

For any record with a missing account ID, I want to populate it with the most common account ID for the customer (based on sales).

Cust_ID	Acct_ID	Sales
225	ABC	10
225	ABC	50
225	DEF	0
225		10
225		20
588	XYZ	500

So for Customer 225, the most common Account ID (based on sales) is ABC. I'd want to add a column like this.

Cust_ID	Acct_ID	Sales	Final_Acct_ID
225	ABC	10	ABC
225	ABC	50	ABC
225	DEF	0	ABC
225		10	ABC
225		20	ABC
588	XYZ	500	XYZ

user_18345037 · Answer

Final_Acct_ID = 
VAR tbl = 
CALCULATETABLE(
FILTER(
    ADDCOLUMNS(  
        SUMMARIZE( 'Table1', 'Table1'[Cust_ID], 'Table1'[Acct_ID]), 
        "@count",
        CALCULATE( COUNT('Table1'[Acct_ID])) 
    ), 
    'Table1'[Acct_ID] <> BLANK()
),

ALLEXCEPT('Table1','Table1'[Cust_ID]))

RETURN 

MAXX(TOPN(1,tbl,[@count]), 'Table1'[Acct_ID])

Cust_ID	Acct_ID	Sales	Final_Acct_ID
225	ABC	10	ABC
225	ABC	50	ABC
225	DEF	0	ABC
225		10	ABC
225		20	ABC
588	XYZ	500	XYZ

Cust_ID	Acct_ID	Sales	Final_Acct_ID
225	ABC	10	ABC
225	ABC	50	ABC
225	DEF	0	ABC
225		10	ABC
225		20	ABC
588	XYZ	500	XYZ

PowerPivot DAX - Creating column based on most common value in another column

1 Answers

Cust_ID	Acct_ID	Sales	Final_Acct_ID
225	ABC	10	ABC
225	ABC	50	ABC
225	DEF	0	ABC
225		10	ABC
225		20	ABC
588	XYZ	500	XYZ