CODEWITHSUNDEEP
powerbidax
Carto_
Carto_

Reputation: 597

DAX - Get list from a filtered SUMMARIZE formula

So, I have the following tables in my Power BI :

Sales : Date | ID_Client | ID_Product | Amount
Client : ID_Client | Name_Client

I would like to get the number of unique BIG clients in any given month. I therefore use the following formula (which I then put in a column in a table with months in rows):

# BIG Clients =
VAR threshold = 10000
RETURN
     (
        CALCULATE(
            DISTINCTCOUNT( Sales[ID_Client] ),
            FILTER(
                SUMMARIZE(
                    Sales,
                    Sales[ID_Client],
                    "Sales", SUM( Sales[Amount] )
                ),
                [Sales] >= threshold
            )
        )
    )

QUESTION IS : how can I get the list of those BIG clients for any given month? Let's say I click on the November number of big clients in my table, could another table nearby display the list of those clients ?

Thanks in advance for your kind help, I've been trying for a while :)

Upvotes: 1

Views: 1042

Answers (2)

sergiom
sergiom

Reputation: 4887

I assume that you have a table of clients with the Name column with a one to many relationship with the Sales table and that you do not have duplicate client names. Then you may create a [BIG Sales] measure to be used in a table or matrix visual with client names on the rows.

since [BIG Sales] evaluates to BLANK() for clients with less that threshold sales, they are automatically filtered out from the visual

BIG Sales =
VAR threshold = 10000
VAR BigCustomers =
    FILTER(
        ADDCOLUMNS(
            VALUES( Clients[Name] ),
            "Sales", SUM( Sales[Amount] )
        ),
        [Sales] >= threshold
    )
RETURN
    SUMX(
        BigCustomers,
        [Sales]
    )

Upvotes: 1

Alexis Olson
Alexis Olson

Reputation: 40204

You could create a table or matrix visual with the client on the rows and use your measure in the values field. This will show 1 for all big clients and return blank for the rest (which should hide them). If you don't want to show the measure, you can set the value is 1 in the filter pane and remove the measure from the values field.

Big Client filter

A more direct option is to use a simple SalesAmount = SUM ( Sales[Amount] ) measure in the values field and filter like this

SalesAmount filter

Upvotes: 0

Related Questions