Create New Calculated Table From Existing Table With Only Maximum Value For Each Customer

[rbowen]

I have an existing table in my BI Desktop report that has multiple customer accounts and the various store locations they've made purchases from. Here's an example of what it looks like for just two of the customer accounts:

What I'm trying to do is create a new table where the results only contain what store each customer has the most transactions with. So, in the example above, the new table would contain only one result per customer account and would like like this:

I've tried using various DAX functions to get this done but having no luck. At best, I can get multiple rows per customer showing a summed result for each store they've done transactions with, but not limited to only the store they've used the most. Any guidance would be appreciated.

Thank you.

[Ashish_Mathur]

Hi,

Would you be OK with a Power Query solution?  If yes, then share data in a format that can be pasted in an MS Excel file.

[v-xinruzhu-msft · Accepted solution]

Hi @rbowen 

Yo can refer to the following code.

Create a calculated table.

Table 2 =
VAR a =
    SUMMARIZE (
        'Table',
        [Customer Number],
        [Customer Name],
        "Max Tra", MAX ( 'Table'[Transaction Counter] )
    )
RETURN
    ADDCOLUMNS (
        a,
        "Max Store Number",
            MAXX (
                FILTER (
                    'Table',
                    [Customer Number] = EARLIER ( 'Table'[Customer Number] )
                        && [Customer Name] = EARLIER ( 'Table'[Customer Name] )
                        && [Transaction Counter] = EARLIER ( [Max Tra] )
                ),
                [Store Number]
            ),
        "Max Store Name",
            MAXX (
                FILTER (
                    'Table',
                    [Customer Number] = EARLIER ( 'Table'[Customer Number] )
                        && [Customer Name] = EARLIER ( 'Table'[Customer Name] )
                        && [Transaction Counter] = EARLIER ( [Max Tra] )
                ),
                [Store Name]
            )
    )

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rbowen]

Thank you Zhu, that worked perfectly!