Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ssbagley
Helper III
Helper III

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 IDDateAmount
11/2/2023 $  500.00
22/4/2023 $  875.00
32/5/2023 $  810.00
33/1/2023 $  100.00
41/17/2023 $  715.00
51/20/2023 $  972.00
62/7/2023 $  300.00
62/8/2023 $  550.00


Desired Result:

Apply filter (to visual or not): Amt per person > $800.00

Emp IDAmount
2$875
3$910
5$972
6$850

 

 

 

 

 

4 ACCEPTED SOLUTIONS
Padycosmos
Solution Sage
Solution Sage

Hope this helps:

Padycosmos_0-1678314580608.png

 

View solution in original post

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

andhiii079845_0-1678391702334.png
Filter = 
VAR _t = CALCULATE(sum(Expenses[Amount]),ALLEXCEPT(Expenses,Expenses[Emp No]))
Return _t
You can it change it easy to a Filter for the filter pane:
Filter = 
VAR _t = CALCULATE(sum(Expenses[Amount]),ALLEXCEPT(Expenses,Expenses[Emp No]))
RETURN IF(_t>800,1,0)
 You can than choose "equal or greater than" 1.
 
Did it help?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

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!




View solution in original post

bolfri
Super User
Super User

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.

bolfri_0-1678393101033.png

Or add more columns to see details.

bolfri_1-1678393157439.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

29 REPLIES 29

Do you have any thoughts on how I can only display the people > $800?  When I add the measure to a filter and ask to show "all not blank", I receive an error.  

Hi,

you want this:

andhiii079845_0-1678301708835.png

Without the measure? 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Yes. I ultimately have to address the people with totals over $800, so I need a way to limit the results to only the people I'm concerned about.

Change the filter measur like this:

Filtered Emplpoyees =
COUNTAX(FILTER(ppl,[Employee Total Expenses] >= 800),'Table'[Emp ID])

But this measure in the filter pane and "greater than or equal to 1"




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@andhiii079845 I'm editing my response, because I don't think it did actually work.  It limited the results to greater than $800; however, the lines included in the results are only individual submissions of $800 or more.  

 

So it's showing me $800 x 3 = "total" of $2400, but I also need the individual submissions that are less than $800 but bring the employee total over $800, like $750 + $100 = $850.

 

I really thought this was it! 😞

please show us with a screenshot the problem. 🙂

Do you mean perhaps per employee id and month over 800 ? 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




No - I need total by person, but I need to be able to find the details behind the total without running a separate report. Here's the screenshot:

 

2023-03-08_18-58-21.jpg

Hi, sorry for the confusion.
The problem is that you do not give a full example with all involved columns. Please add a new data example with all involved columns and tables(!) (when you add more columns to the report) and a screenshot which columns you use in the matrix visual. MEASURES are very sensitive which columns you use in the matrix. Please also involved slicer if you want to use it. 
In the best case please create a example pbi file with no sensitive data. 
Than i will check what is possible. 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Before I do that - The measure you provided works. Is there a way I could create another table that would provide the "detail" behind the employee totals? The detail would include transaction dates, individual amounts, expense names, etc. See screenshot - I can't make the totals agree.

Or make the measure a new table instead that could be used to reference in order to get to the detail? 

 
Original measure -
Filtered Emplpoyees =
COUNTAX(FILTER(ppl,[Employee Total Expenses] >= 800),'Table'[Emp ID])

But this measure in the filter pane and "greater than or equal to 1"
 
2023-03-09_10-15-08.jpg

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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