CODEWITHSUNDEEP
daxpowerpivot
waealu
waealu

Reputation: 331

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

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

Upvotes: 1

Views: 137

Answers (1)

davidebacci
davidebacci

Reputation: 30174

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])

enter image description here

Upvotes: 1

Related Questions