Adding up subsequent Sites and checking the limit to be lower than 100,000

[Datagulf]

Hi Community,

I have a table(Accounts) that has the company's names and the respective site names. These companies could have many sites or even one site. Each site name has it is billed Amount as shown below.

COMPANY NAME	SITE NAME	Billed Amount	Entry Date
Company A	HQ	13000	1/4/2021
Company A	New York	67000	24/5/2021
Company A	Seattle	45000	1/6/2021
Company B	California	10700	10/6/2021
Company C	Florida	23000	27/6/2021
Company C	California	11000	1/7/2021
Company D	Toronto	133000	13/7/2021
Company D	New York	74000	21/7/2021
Company D	Seattle	13000	28/7/2021
Company E	Toronto	127000	7/8/2021

 I am creating a measure that adds all Billed amounts for the company names and displays the value above 100K in a matrix table. I was using this formula below but it shows inconsistencies when I add a date filter. 

Top 100K accounts  = Sumx(filter(summarize(Accounts, Accounts[Site_name], "_1", sum(Accounts[Billed Amount])), [_1] >100000), [_1])

I have tried to use the filters too, and it is not correct but I accept all answers as long as they work. Thanks in advance.

[Greg_Deckler · Accepted solution]

@Datagulf Perhaps try:

Top 100K accounts = 
  SUMX(
    FILTER(
      GROUPBY(
        'Accounts', 
        'Accounts'[Site_name], 
        "_1", SUMX(CURRENTGROUP(),'Accounts'[BilledAccount])
      ),
      [_1] > 100000
    ),
    [_1]
  )

Otherwise, need more information on the inconsistencies.

[Greg_Deckler · Accepted solution]

@Datagulf Perhaps try:

Top 100K accounts = 
  SUMX(
    FILTER(
      GROUPBY(
        'Accounts', 
        'Accounts'[Site_name], 
        "_1", SUMX(CURRENTGROUP(),'Accounts'[BilledAccount])
      ),
      [_1] > 100000
    ),
    [_1]
  )

Otherwise, need more information on the inconsistencies.

[Datagulf · Accepted solution]

@Greg_Deckler this works but with Accounts['Company Name']. Thanks