Create an "Other" line item

[JP0077]

Hi all,

I have a matrix that contains a list of names and the number of total transactions during the month for each. I have various names that only had 1 transaction all month. How do I group all the ones that had 1 transaction into an "Other" category and have it still show in the matrix? The # of Transactions is not a formula, it's just a count. All the data is found in the same table.

Thanks for the help?

[danextian · Accepted solution]

Hi @JP0077 ,

For this, I would create a separate dates table that contains the distinct clients + another row for Other either in DAX or M and then create a measure to return the value for each client based on your desired logic.

Client = 
VAR __CLIENTS =
    DISTINCT ( 'Table'[Client] )
VAR __OTHER =
    ROW ( "Client", "Other" )
RETURN
//sort added so "Other" is the last item
    ADDCOLUMNS (
        UNION ( __CLIENTS, __OTHER ),
        "Sort", IF ( [Client] = "Other", 1000, 1 )
    )

Sum of Transactions2 = 
VAR __SELECTED_CLIENT =
    SELECTEDVALUE ( Client[Client] )
VAR __T1 =
    //clients with only 1 transaction
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE ( 'Table', 'Table'[Client] ),
            "@transactions", [Sum of Transactions]
        ),
        [@transactions] = 1
    )
VAR __1 =
    //sum of transctions of clients with only 1 transaction
    SUMX (
        __T1,
        [@transactions]
    )
VAR __SELECTED_CLIENTS =
    VALUES ( Client[Client] )
RETURN
    IF (
        __SELECTED_CLIENT = "Other",
        __1,
        IF (
            CALCULATE (
                [Sum of Transactions],
                KEEPFILTERS ( 'Table'[Client] IN __SELECTED_CLIENTS )
            ) <> 1,
            CALCULATE (
                [Sum of Transactions],
                KEEPFILTERS ( 'Table'[Client] IN __SELECTED_CLIENTS )
            )
        )
    )

Please see attached pbix for the details