Forum Discussion

rbowen's avatar
rbowen
Helper III
2 years ago
Solved

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

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.

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      rbowen
      Helper III
      2 years ago

      Thank you Zhu, that worked perfectly!

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    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.