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.
Hi All
I am currently having a problem in showing transactions ( the transactions are whole number value amounts) that fall between a start and end date for an employee. The crux of the problem is that the start and end date differ for each employee, so filtering for the data is a problem. I have explained the full issue below:
I have two tables that I need to link between. One is a list of all transactions made by each employee (employee name, HR number, value, date) , the other is a listing of the start and end date of each employee (employee name, HR number,start date, end date), What I want to show on the the desktop is a the sum total of the transactions by employee between the start and end date and then ideally a breakdown below of each transactions that makes up that sum total in a seperate table that is filtered by the employee using the dynamic filter.
So far I have managed to work out the sum total by adding on a new colum to the start and leave date table and using the following formula :
Total spend = CALCULATE (
SUM( Transactions[Total] ),FILTER(Transactions, Transactions[HR number] = 'HR start end date'[HR number]),DATESBETWEEN(Transactions[Date],
'HR start end date'[Start date],
'HR start end date'[End date]
))
'Transactions' is my transactions table
'HR start end date' is my star and end date table
This forumla gives me my total figure but I cannot get it so that I can see the transactions that make up the total amount on the desktop page even with a relationship between the two tables as it just shows the total transactions by the employee and does not filter it for the start and end date.
I keep thinking there must be a better way of doing this that uses the PowerBI desktop features but I cannot come up with it.
I cannot upload anything to help as it has sensitve data on it.
Any help would be much appreciated!
Regards
Dan
Solved! Go to Solution.
Hi
This should be so easy. Im unsure why you are having a problem doing this
Just set up a column chart with dimension = staffname and sum (Amount)
and a matrix / or table with the required dimensions and sum(Amount)
click on the bar chart as required to drill down
edit But you realy need to be a lot clearer as to what you want or what your problem is. Im still not sure
Thank you both for your help. To clarify in a little more detail, I have managed to get the following data using the calculate formula I attached above that shows the sum amount of transactions that falls between the start and end date, but what I would like to do is show the individual transactions that make up the sum amount.
I currently have :
employee name | Start date | end Date | Total of transactions |
John doe | 01/02/2014 | 23/04/2015 | £15,560 |
What I would also like to show as well is the transactions that make up that amount
Transaction amount | Transaction date | Description | Reference |
1000 | 14/02/2014 | Purchase | 1231 |
14000 | 16/07/2014 | card payment | 4123 |
560 | 16/07/2014 | Cash | 1231 |
Ideally on a powerBI desktop page, what I would want is a bar chart showing the spend by individual(from the data in the top table) that I could click on that would then filter a transactions table underneath to show what transactions make up that total amount.
It may not be possible, but at the moment I'm having to manually filter for the dates for the relevant employee to show the transactions that make up the total sum of transactions for that period and I'd like to be able to do it automatically
Hi
This should be so easy. Im unsure why you are having a problem doing this
Just set up a column chart with dimension = staffname and sum (Amount)
and a matrix / or table with the required dimensions and sum(Amount)
click on the bar chart as required to drill down
edit But you realy need to be a lot clearer as to what you want or what your problem is. Im still not sure
I read this but am unsure what you want to do
I assume you want to filter by a transaction period?
Why not enter in a table
Employee Name
Start Date
End Date
Sum (Amount)
and filter as required
Admittedly only having read this briefly, what I think that you should check out would be the new CALCULATETABLE function.
If I understood what you are going for, this might fit the bill.
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |