cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Responsive Resident

## 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

2 ACCEPTED SOLUTIONS
Community Support

Hi, @Datagulf

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.

Community Support

Hi, @Datagulf

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 7
Super User

@Datagulf , 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])))

Responsive Resident

@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..

Community Support

Hi, @Datagulf

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

Responsive Resident

THanks @v-easonf-msft

Community Support

Hi, @Datagulf

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.

Responsive Resident

@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?

Community Support

Hi, @Datagulf

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors