Calculate sum by date and type

[PowerMyBI]

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

[v-rzhou-msft]

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

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. 

[edhans]

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.

[PowerMyBI]

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. 

Thanks again for your reply.

[edhans · Accepted 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

[PowerMyBI]

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

[PowerMyBI]

Think I managed to figure it out:

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