cancel
Showing results for
Search instead for
Did you mean:
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 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

4 ACCEPTED SOLUTIONS
Solution Sage

Hope this helps:

Solution Sage

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?
Solution Sage

My fault by copying:

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

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.

29 REPLIES 29
Helper III

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.

Solution Sage

Hi,

you want this:

Without the measure?

Helper III

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.

Solution Sage

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"
Helper III

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

Solution Sage

please show us with a screenshot the problem. 🙂

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

Helper III

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:

Solution Sage

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

Helper III

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"

## Helpful resources

Announcements

#### Power BI May 2023 Update

Find out more about the May 2023 update.

#### Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors
Top Kudoed Authors