Working with filters in measures

[Anonymous]

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-easonf-msft · Accepted solution]

Hi, @Anonymous 

If I understand you correctly, you can create a measure and drag it to visual filter pane as follows:

Measure1 = 
VAR a =
    CALCULATE (
        SUM ( Sheet1[Billed Amount] ),
        ALLEXCEPT ( Sheet1, Sheet1[Client ] )
    )
RETURN
    IF ( a > 10000, 1, 0 )

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Try a measure like

Measure =
var _1 = calculate(sum('Sheet1'[Billed Amount]) ,filter('Sheet1', 'Sheet1'[Client] = max('Sheet1'[Client])))
return
sumx(filter( values('Sheet1'[Client]), _1 >10000), calculate(sum('Sheet1'[Billed Amount])))

[Anonymous]

@amitchandak , this measure does the same as the one I have.. Is there a way to have all sites to be there as long as account adds up to more than 10K.
Like the value for Sites should be seen as long as the accounts add up to more than 10K..Let me show you what it brings. 

As you can see the sites like for client B,and New York site shows 13K..

[v-easonf-msft]

Hi, @Anonymous 

Could you please tell me whether your problem has been solved?

If you still need help, please share more details.

If it is solved, you could share your solution here and accept it as solution to close this thread.

Best Regards,
Community Support Team _ Eason

[Anonymous]

THanks @v-easonf-msft 

[v-easonf-msft · Accepted solution]

Hi, @Anonymous 

If I understand you correctly, you can create a measure and drag it to visual filter pane as follows:

Measure1 = 
VAR a =
    CALCULATE (
        SUM ( Sheet1[Billed Amount] ),
        ALLEXCEPT ( Sheet1, Sheet1[Client ] )
    )
RETURN
    IF ( a > 10000, 1, 0 )

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-easonf-msft , thanks. From the solution, I can see the table is okay but I get a different table when I apply the same. Don't know why.

Does the table created impact on anything?

[v-easonf-msft · Accepted solution]

Hi, @Anonymous 

If it is convenient, please show some relevant screenshots or your current file.

Do you have other filters/slicers to apply to this visual?

The measure I created is only uesed to filter the rows that meet the conditions in the original table, and will not affect the value of  Billed Amount. In addition, please check whether the fields used in your matrix are consistent with mine.

Best Regards,
Community Support Team _ Eason