Show all elements in dropdown despite filters in measure

[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 

[DataInsights · Accepted solution]

@Datagulf,

Try these measures:

Sum Billed Amount = SUM (Table1[Billed Amount($)] )

Top 10K Accounts = 
VAR vTable =
    ADDCOLUMNS ( VALUES ( Table1[Client Name] ), "@Amount", [Sum Billed Amount] )
VAR vTable10K =
    FILTER ( vTable, [@Amount] >= 10000 )
VAR vResult =
    SUMX ( vTable10K, [@Amount] )
RETURN
    vResult

[DataInsights · Accepted solution]

@Datagulf,

Try these measures:

Sum Billed Amount = SUM (Table1[Billed Amount($)] )

Top 10K Accounts = 
VAR vTable =
    ADDCOLUMNS ( VALUES ( Table1[Client Name] ), "@Amount", [Sum Billed Amount] )
VAR vTable10K =
    FILTER ( vTable, [@Amount] >= 10000 )
VAR vResult =
    SUMX ( vTable10K, [@Amount] )
RETURN
    vResult

[Datagulf · Accepted solution]

Hello, @DataInsights . Much appreciated this answer. However, I cannot see the lower values of less than 10K when drilling down to the sites. However, I went for another workaround. That is to create full-page tooltips and show the items without leaving out the ones that are below 10K. 

thanks.