cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculate sum by date and type

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
1 ACCEPTED SOLUTION
Super User III

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
``````

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
6 REPLIES 6
Community Support

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:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/tongzhou_qiuyunus_onmicrosoft_com/EZO8SmV8bPpFuyye...

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.

Super User III

You don't need to do much.

1. Drop the date into a table
2. Drop the name next to it
3. Put this measure as the third value
``Total = SUM('Table'[Units])``

Power BI will do the rest. See this sample PBIX file.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Frequent Visitor

Hi @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.

Super User III

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
``````

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Frequent Visitor

Thanks @edhans and @RicoZhou

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.

Frequent Visitor

Think I managed to figure it out:

`SumPerStaffAndDay = CALCULATE(SUM(Table1[Units]), ALLEXCEPT(Table1, Table1[Date].[Date], Table1[StaffName]))`

Announcements