Forum Discussion

PowerMyBI's avatar
PowerMyBI
Icon for Helper I rankHelper I
6 years ago
Solved

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
Need Help
  • edhans's avatar
    edhans
    6 years ago

    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

     

6 Replies

  • PowerMyBI's avatar
    PowerMyBI
    Icon for Helper I rankHelper I
    6 years ago

    Think I managed to figure it out:

     

    SumPerStaffAndDay = CALCULATE(SUM(Table1[Units]), ALLEXCEPT(Table1, Table1[Date].[Date], Table1[StaffName]))
  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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's avatar
      PowerMyBI
      Icon for Helper I rankHelper I
      6 years ago

      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's avatar
        edhans
        Icon for Community Champion rankCommunity Champion
        6 years ago

        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

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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/EZO8SmV8bPpFuyyeFNrMchABlYiUGbW5ChHJJbw6cEks-A?e=Bh8k0d

     

    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.