- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Subtotal of Amount by Person
I've been researching this all day and have had no luck.
I am dealing with employee expenses and I am connected via Direct Query. I need to identify every employee with total expenses over $800 which means my measure needs to be able to filtered to > $800. Everything I have tried only gives me individual transactions over $800, but I need the total by person that is over $800.
Thoughts?
Data Table:
Emp ID | Date | Amount |
1 | 1/2/2023 | $ 500.00 |
2 | 2/4/2023 | $ 875.00 |
3 | 2/5/2023 | $ 810.00 |
3 | 3/1/2023 | $ 100.00 |
4 | 1/17/2023 | $ 715.00 |
5 | 1/20/2023 | $ 972.00 |
6 | 2/7/2023 | $ 300.00 |
6 | 2/8/2023 | $ 550.00 |
Desired Result:
Apply filter (to visual or not): Amt per person > $800.00
Emp ID | Amount |
2 | $875 |
3 | $910 |
5 | $972 |
6 | $850 |
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you it makes it now more easier than 1000 words. 🙂
Please try this. The filter calculated it in the group of the Employee No.:
Filter =
VAR _t = CALCULATE(sum(Expenses[Amount]),ALLEXCEPT(Expenses,Expenses[Emp No]))
Return _t
Filter =
VAR _t = CALCULATE(sum(Expenses[Amount]),ALLEXCEPT(Expenses,Expenses[Emp No]))
RETURN IF(_t>800,1,0)
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
My fault by copying:
Filter =
VAR _t = CALCULATE(sum(Expenses[Amount]),ALLEXCEPT(Expenses,Expenses[Emp No]))
RETURN IF(_t>800,1,0)
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Total Exp:
Total Exp =
var emp = VALUES(Expenses[Emp No])
return
SUMX(
SUMMARIZE(
FILTER(ALLSELECTED(Expenses),Expenses[Emp No] in emp),
Expenses[Emp No],
"Total",SUM(Expenses[Amount])
),
[Total]
)
Result:
You can put it on visual level to work on aggregated items.
Or add more columns to see details.
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Total Exp:
Total Exp =
var emp = VALUES(Expenses[Emp No])
return
SUMX(
SUMMARIZE(
FILTER(ALLSELECTED(Expenses),Expenses[Emp No] in emp),
Expenses[Emp No],
"Total",SUM(Expenses[Amount])
),
[Total]
)
Result:
You can put it on visual level to work on aggregated items.
Or add more columns to see details.
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you! This also worked for me (for all the future people searching for answers!)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Make a example (sample data) how this related tables are in connection with the main table (amount) and i can check it to find a general and flexible solution.
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sample data: https://drive.google.com/file/d/1vyqpCH07sEYeOhGRnHdy9qA5RaIDvGtv/view?usp=share_link
(Now I cannot get your measure to work at all - 😩 )
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you it makes it now more easier than 1000 words. 🙂
Please try this. The filter calculated it in the group of the Employee No.:
Filter =
VAR _t = CALCULATE(sum(Expenses[Amount]),ALLEXCEPT(Expenses,Expenses[Emp No]))
Return _t
Filter =
VAR _t = CALCULATE(sum(Expenses[Amount]),ALLEXCEPT(Expenses,Expenses[Emp No]))
RETURN IF(_t>800,1,0)
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Trying it all now. The first one works beautifully (like, literally excited about it!!!). The second, I'm getting "The syntax for 'IF' is in correct."
I cannot thank you enough!!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
My fault by copying:
Filter =
VAR _t = CALCULATE(sum(Expenses[Amount]),ALLEXCEPT(Expenses,Expenses[Emp No]))
RETURN IF(_t>800,1,0)
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Any idea why the measure above would stop working? When I try to add as a filter now, my table visual errors out.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you‼️‼️‼️
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Please set @Padycosmos post also as a solution, I see he mentioned it some days before 🙂
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hope this helps:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you for your reply! This gave me the same total on every line. ☹️
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Using a matrix, if I only have Emp ID and Name in the Rows and (Sum of)"Amount" in the values, then the measure works like a charm. But, if I add date or any other details, it limits the "Transaction Amount" to $800+. I have one person that has 28 expenses that make up a total of $2,225.03, but if I try to get to the detail, I can't.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Do you need a table like that or need a visual showing filtered employees?
Regardless, you can do the followings.
1. Filter in a visual
2. Filtered employees table
Filtered Emplpoyees =
FILTER(
SUMMARIZE(
'Table',
'Table'[Emp ID]
),
[Employee Total Expenses] >= 800
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you for your response. When I add the Total to a visual filter, it just gives me the individual submisions made for $800+, not the total.
I tried the table but clearly do not have it set up correctly - would this measure be added as a filter or a column in a Matrix? Sorry - trying to determine how to use.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
1. Build the measure in my screenshot I attached previously
2. Bring in EmpID into a table/matrix visual
3. Bring in the measure into the visual
4. The filter on that measure has been added to the visual
5. Filter greater than 800 in the filter
Does this help...?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I am trying to get this to work. Direct Query with a TON of data - trying to filter it down so it will refresh in a timely manner at least for testing purposes.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
measure =
Make the post as a solution if it was helpfull.
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thiis works, until I try to add a filter to remove the blanks (ie. employees whose expenses are not >$800). I will keep playing with it though. Thank you for your reply!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
let us know if you need more help. mark a or two post as solution. 🙂
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
Anonymous
| 12-12-2021 07:31 AM | ||
07-05-2024 12:02 PM | |||
11-27-2024 01:23 PM | |||
09-19-2024 08:54 AM | |||
08-17-2022 08:56 PM |
User | Count |
---|---|
126 | |
81 | |
59 | |
57 | |
43 |
User | Count |
---|---|
183 | |
111 | |
82 | |
66 | |
51 |