Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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))
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?
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
Hi @jbrijalba ,
Could you pls show me part of your data?I can't test without data...
Best Regards,
Kelly
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |