Forum Discussion
Working with filters in measures
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
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.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
7 Replies
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])))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..
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.
