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

     

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

 

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

    Thanks edhans and Anonymous 

    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.