I have been struggling with a problem for quite a while now. I have mainly been using Excel to go around this every time but I'm sure it can be done and automated in Power BI which is what I need help with. I will highlight my problem and step by step process I do to achieve my desired result and hopefully someone can help translate this to Power BI.
Name Segment1 Segment2 AccountID
Apple Technology Entertainment ABCD
Apple Technology Entertainment ABCD
Apple Telecom Services PWRK
Banana Banking Finance LITE
Banana Banking Finance LITE
Banana Banking Finance LITE
Banana Banking Services ITER
Zebra Energy TRWD
Zebra E-commerce WERD
Zebra Services AWED
Qwerty TRUC
Qwerty Services Telecom TYRE
Qwerty Trade MIGS
Image 1 is what my starting table looks like. The objective is to return only 1 row per 'Name'. First rule should be the Name, Segment 1, Segment 2 with the max number of 'Account ID' under it. The second is if incase there's a tie, we should get any or the row with non blank Segment 1 and Segment 2 (whatever is easier).
Name Segment1 Segment2 Count of Account ID
Apple Technology Entertainment 2
Apple Telecom Services 1
Banana Banking Finance 2
Banana Banking Services 1
Zebra Energy 1
Zebra E-commerce 1
Zebra Services 1
Qwerty 1
Qwerty Services Telecom 1
Qwerty Trade 1
In the second step I take a count of each Account under said Name, Segment 1, Segment 2.
Name Max of Count
Apple 2
Banana 2
Zebra 1
Qwerty 1
In the third step, I take max of count of Account under said Name.
Name Max of Count of Account ID Segment1 Segment2
Apple 2 Technolohgy Entertainment
Banana 2 Banking Finance
Zebra 1 Energy
Qwerty 1 Services Telecom
This is the final desired output. Continuing from the third step, I applied a lookup on 'Name & Max of Acc ID' (Create a new ID column) against Image 2 (where I also create 'Name & Max of Acc ID' field, and return Segment 1 and Segment 2. The only manual adjustment I have made here is input Segment 1 and Segment 2 for 'Qwerty' as it had a tie on all 3 segments and I wanted the non blank ones. If this is too difficult, I am fine with returning first match or whatever here.
Would be grateful if someone can help out!
I have tried solving this problem using a DAX measure or Power Query but my Power BI skills are nowhere like my Excel skills. I managed to make some progress but still getting stuck midway.