Filtering using an aggregation

[jbrijalba]

I have a report looking at credit card spending for the company I work at. The CAO wants to see employees who spent over $20,000 over a period (ex: Dec 21st thru Jan 20th). I put a filter on spend amount is greater or equal to $20,000 for the visualization. What happens, is only transactions with amounts over $20,000 show up. I need it to be an aggregated amount per employee that spends over $20,000. 

Have slicer for period.

Have matrix with employee spend.

Need an aggregated filter on spend over $20,000 that shows employees spend over $20,000, not transaction level.

Please help. Don't know why I can't figure this out my self. Check out pic for more support/info.

[amitchandak]

Try like

calculate(countx(filter(SUMMARIZE(
data,
data[employee ID],
"sum_exp",
Sum( 'Date'[expense]),
"Max_ID", max(data[employee ID])
),sum_exp>20000), Max_ID))

[jbrijalba]

Didn't work. I should have mentioned that in the Matrix I have Carholder name and Vendor name in the row slot and amount in the value. Sorry for leaving that out.

Also, was it supposed to give me 1s and 0s?

[amitchandak]

Refer this formula.

Total value = Calculate(
    count(data[employee ID]),
    FILTER(  
        ADDCOLUMNS( 
            SUMMARIZE(
                data,
                data[employee ID],
				"sum_exp",
				Sum( 'Date'[expense]),
                "Max_ID", max(data[employee ID])
            ),
            "Over20K",
            calculate(
                sum(data['Date'[expense]),
                filter(
                    all(data),
                    data[employee ID]= [Max_ID]  )
                )
            )
        ,[Over20K]>20000
    )
)

In this formula, you can use summarize to build your filter not the value. so value should work at different level

[v-kelly-msft]

Hi @jbrijalba ，

Could you pls show me part of your data?I can't test without data...

Best Regards,

Kelly

[jbrijalba]

It is extremely confidential and would take a while to make a dummy dataset. I ended up removing vendor from the matrix and using the advanced filter >= 20,000. Works but do not have the drill down capabilities but the graph is of vendor spend so it works, sort of. I will provide dummy data if I find time. Thank you for your help.