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 Power BI community,
I am trying to calculate the sum of units recorded each day by staff member. Effectively I have many columns of transaction data pulled from our practice management platform. There are several transaction lines per day and I need to get a total amount as per example table. Could anyone point me in the right direction? Thank you in advance.
StaffName | Units | Date | Calculated Sum per staff and day |
Bob | 2.5 | 7/21/2020 | 8 |
Anne | 3 | 7/21/2020 | 8.5 |
Bob | 5.5 | 7/21/2020 | 8 |
Anne | 2.5 | 7/21/2020 | 8.5 |
Anne | 3 | 7/21/2020 | 8.5 |
Bob | 1.5 | 7/20/2020 | 7.5 |
Bob | 4 | 7/20/2020 | 7.5 |
Anne | 8 | 7/20/2020 | 8 |
Bob | 2 | 7/20/2020 | 7.5 |
Solved! Go to Solution.
Great @PowerMyBI - Glad you got it figured out. I misunderstood your original request. For grins I went back and revised my measure and this returns the same. Be sure to mark one as the solution so this thread can be marked as solved.
Total 2 =
VAR varCurrentName =
MAX( 'Table'[StaffName] )
VAR varCurrentDate =
MAX( 'Table'[Date] )
VAR Result =
CALCULATE(
SUM( 'Table'[Units] ),
FILTER(
ALL(
'Table'[Date],
'Table'[StaffName],
'Table'[Units]
),
'Table'[Date] = varCurrentDate
&& 'Table'[StaffName] = varCurrentName
)
)
RETURN
Result
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @PowerMyBI
I am so glad you have solved your problem. I use different ways to solve your problem and you may have a try.
1. Calculated Column:
Column =
CALCULATE(SUM(Table1[Units]),FILTER(Table1,Table1[StaffName]=EARLIER(Table1[StaffName])&&Table1[Date]=EARLIER(Table1[Date])))
Result is as below:
2. Measure:
Calculated Sum per staff and day =
CALCULATE(SUM(Table1[Units]),FILTER(ALL(Table1),Table1[StaffName]=MAX(Table1[StaffName])&&Table1[Date]=MAX(Table1[Date])))
Result is as below:
You can download the pbix file form this link:
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
You don't need to do much.
Total = SUM('Table'[Units])
Power BI will do the rest. See this sample PBIX file.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @edhans
Thank you for your reply. I wanted to add the values to a volumn so I could use in a visual and further reference the data such as a count of times a staff member exceeds a certain number of hours per week and also the average total units recorded each day per staff member.
Thanks again for your reply.
Great @PowerMyBI - Glad you got it figured out. I misunderstood your original request. For grins I went back and revised my measure and this returns the same. Be sure to mark one as the solution so this thread can be marked as solved.
Total 2 =
VAR varCurrentName =
MAX( 'Table'[StaffName] )
VAR varCurrentDate =
MAX( 'Table'[Date] )
VAR Result =
CALCULATE(
SUM( 'Table'[Units] ),
FILTER(
ALL(
'Table'[Date],
'Table'[StaffName],
'Table'[Units]
),
'Table'[Date] = varCurrentDate
&& 'Table'[StaffName] = varCurrentName
)
)
RETURN
Result
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThanks @edhans and @v-rzhou-msft
I'm learning quite quickly that there are many different ways to achieve the same outcome in Power BI. I appreciate both your replies and I'll have a go at replicating both methods and check for any variance against my own solution to make sure there are no mistakes in my working.
Think I managed to figure it out:
SumPerStaffAndDay = CALCULATE(SUM(Table1[Units]), ALLEXCEPT(Table1, Table1[Date].[Date], Table1[StaffName]))
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 |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |