Removing a measure from a filter to show all constituents in a matrix table

[Datagulf]

Hello Community. I have a blocker on a task I am doing. So, I have a table with Internet connection clients. A single client could have one site or multiple sites. Each site is billed a different amount. The data is as below.

Client Name	Sites of Clients	Billed Amount($)
client A	Head office	1000
Client A	New York	500
Client A	New Orleans	2000
Client A	Vegas	3500
Client A	Seattle	5000
Client A	Florida	4500
Client B	New York	13000
Client B	Seattle	2000
Client B	Florida	1500
Client C	Washington	13700
Client C	Head office	3000
Client D	Head office	12000
Client E	Washington	8800
Client F	Florida	11000
Client G	Seattle	7700
Client H	New York	3300
Client H	New Orleans	4000
Client H	Head office	3500
Client I	Seattle	12500

The task is to find the clients that are billed more than 10000$. WHich I have been able to do with the formulae below.

Top 10K accounts = 

SUMX(

FILTER(

GROUPBY(

'Sheet1',

'Sheet1'[Client ],

"_1", SUMX(CURRENTGROUP(),'Sheet1'[Billed Amount])

),

[_1] >= 10000

),

[_1]
)

The problem is the Matrix table only returns the sites that are above 10000, whereas the sites below 10000, also were included in the build-up of the amount to being greater than 10000. If you can help change the formula to account for the Companies with a total of 10000 change, I would be grateful. The Pbix is below. Thanks in advance. https://drive.google.com/file/d/1uRGQrGHWK8ZRX5ywCCangyflrMq3d-P8/view?usp=sharing 

[v-yiruan-msft]

Hi @Datagulf ,

I updated your sample pbix file(see attachment), please check whether that is what you want. You can update the formula of your measure [Top 10K accounts] with the same one in measure below:

Billed amount more than 10000 = 
VAR _tab =
    SUMMARIZE (
        'Sheet1',
        'Sheet1'[Client ],
        'Sheet1'[Sites ],
        "@billamount", SUM ( 'Sheet1'[Billed Amount] )
    )
RETURN
    SUMX ( FILTER ( _tab, [@billamount] >= 10000 ), [@billamount] )

If the above one still can't help you get the correct result, please provide the backend scenario and calculation logic with special examples. Thank you.

Best Regards

[Datagulf]

Thanks @v-yiruan-msft . However, this does not solve it. I wanted the table to show all sites as long as the total is greater than 10K. However, that does not happen since only sites  with greater 10K are the only ones shown. However, I got a work around by using a tooltip to show all the sites as long as the client billing goes above 10K.

[v-yiruan-msft · Accepted solution]

Hi @Datagulf ,

I updated your sample pbix file(see attachment) again,  please check whether that is what you want.

1. Create a measure as below to judge if the total of bill amount is equal or greater than 10000

Flag = 
VAR _tab =
    SUMMARIZE (
        'Sheet1',
        'Sheet1'[Client ],
        "@billamount",
            CALCULATE (
                SUM ( 'Sheet1'[Billed Amount] ),
                ALLEXCEPT ( 'Sheet1', 'Sheet1'[Client ] )
            )
    )
RETURN
    IF ( SUMX ( _tab, [@billamount] ) >= 10000, 1, 0 )

2. Apply a visual-level filter with condition (Flag is 1) on the matrix visual just as below screenshot

Best Regards