Address of the customer with maximum sales within the same customer hierarchy group

[DataNinja777]

Hi Power BI Community,

I'd like to show the highest AR balance customer address included in the customer group hierarchy.  The issue is that the customer hierarchy contains multiple locations (addresses) for a single group customer, and concatenated address becomes too long. In order to simplify the disclosure, I'd like to show the address of the customer code (contained in the aggregated higher customer hierarchy group), which has the highest amounts of accounts receivable.  I've written the following dax formula to achieve the aforementioned output, but the concatenated customer address is for multiple locations instead of the top 1.  

The name of the customer and the aggregated value of the accounts receivable contains the multiple location (address) information for one aggregated row, but for address, I'd like to show only the highest address as it gets too long for the concatenated result.   

Thank you for your help!

[DataNinja777]

Thank you @AmiraBedh .  In fact, the reason my formula didn't work yesterday was due to the fact that the measure I was referencing as an order by measure was incorrect, and both your measure and mine produced the same highest sales customer address after changing that part.  

[AmiraBedh]

Glad to help 🙂

[Wilson_]

Hi DataNinja77,

Can you please share a sample pbix file? (If you don't know how, please check the pinned thread in the forum.) It would make debugging your issue easier. 🙂

[AmiraBedh · Accepted solution]

Since you didn't share any data/data model I am assuming the following (please copy/paste your code as text and do not add it as an image) .

I used HighestARAddress to calculate the address with the highest AR balance and with the TOPN to return the top address based on the [AR by customer] measure.
Once out of the top 1 selected, you extract the address itself. MAXX is used here to pull the actual address string, but since TOPN is limited to 1, it effectively grabs the address of the highest AR.
And finally the ALL to ignore any filter context that might limit the addresses considered outside of this calculation.

Highest AR Address =
VAR HighestARAddress = CALCULATE(
    MAXX(
        TOPN(
            1, 
            VALUES(Customer[Address]), 
            [AR by customer], 
            DESC
        ), 
        Customer[Address]
    ),
    ALL(Customer)
)
RETURN
HighestARAddress