Warm tip: This article is reproduced from serverfault.com, please click

DAX

发布于 2020-11-25 15:08:00

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 :)

Questioner
Carto_
Viewed
0
sergiom 2020-11-29 18:05:03

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]
    )