Subtotal of Amount by Person

[ssbagley]

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

[Padycosmos · Accepted solution]

Hope this helps:

[andhiii079845 · Accepted solution]

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

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?

[andhiii079845 · Accepted solution]

My fault by copying:

Filter = 
VAR _t = CALCULATE(sum(Expenses[Amount]),ALLEXCEPT(Expenses,Expenses[Emp No]))
RETURN IF(_t>800,1,0)

[ssbagley]

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.  

[andhiii079845]

Hi,

you want this:

Without the measure? 

[ssbagley]

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.

[andhiii079845]

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"

[ssbagley]

@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! 😞

[andhiii079845]

please show us with a screenshot the problem. 🙂

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

[ssbagley]

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:

[andhiii079845]

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

[ssbagley]

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"