cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PowerMyBI
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.

 

StaffNameUnitsDateCalculated Sum per staff and day
Bob2.57/21/20208
Anne37/21/20208.5
Bob5.57/21/20208
Anne2.57/21/20208.5
Anne37/21/20208.5
Bob1.57/20/20207.5
Bob47/20/20207.5
Anne87/20/20208
Bob27/20/20207.5
1 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

 



Did I answer your question? Mark my post as a solution!
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

View solution in original post

6 REPLIES 6
RicoZhou
Community Support
Community Support

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.png

3.png

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:

1.png

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
Super User III
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])

 

 

edhans_0-1595290398208.png

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

 



Did I answer your question? Mark my post as a solution!
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
PowerMyBI
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. 

 

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

 



Did I answer your question? Mark my post as a solution!
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

View solution in original post

PowerMyBI
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.

PowerMyBI
Frequent Visitor

Think I managed to figure it out:

 

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.