Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to Solution.
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.
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
@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])))
@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, @Datagulf
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
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.
@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?
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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
105 | |
99 | |
98 | |
38 | |
37 |
User | Count |
---|---|
152 | |
120 | |
73 | |
72 | |
63 |