cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

Problem with summing a measure

I have a date range slicer that is being applied to my report.

I have two columns hours1 and hours2 that are being summed to one line depending on the date ranged selected per employee.
I have a quick measure that is dividing these two numbers.

Is there a way to sum the numbers that are being visually displayed?

Summing the quick measure results in a number much higher than anticipated.

1 ACCEPTED SOLUTION

Accepted Solutions
Established Member

Re: Problem with summing a measure

The error is because you are referring naked columns, when Power BI expects an aggregation function wraped. You could just add a SUM around the 2 columns, or, if you already have a measure Hours2 Divided by Hours1, then you could just re-use it:

```Fixed Hours2 Divided by Hours1 =
SUMX ( VALUES ( Table2[Employee] ), [Hours2 Divided by Hours1] )```

6 REPLIES 6
Senior Member

Re: Problem with summing a measure

can you post a sample?

If not, the general way to make the grand total sum to what you see is :

```SUMX (
Values ( Table [Whatever Values you want to sum]),
[measure]
)```
Frequent Visitor

Re: Problem with summing a measure

I apologize for the delayed response, thank you for taking the time to look into this for me. Please see the following of a quick example of what I am trying to accomplish:
Hours1Hours2               Date                              Employee

 8 6.8 Friday, December 29, 2017 Employee 1 8 17.6 Friday, December 8, 2017 Employee 1 8 8.1 Friday, February 2, 2018 Employee 1 8 12.4 Friday, February 9, 2018 Employee 1 8 6.1 Friday, January 12, 2018 Employee 1 8 6.6 Friday, January 19, 2018 Employee 1 8 7.1 Friday, January 26, 2018 Employee 1 8 7.1 Friday, January 5, 2018 Employee 1 8 10.3 Friday, March 18, 2016 Employee 1 8 9.8 Friday, March 25, 2016 Employee 1 8 6.6 Friday, May 11, 2018 Employee 1 8 7.4 Friday, May 18, 2018 Employee 1 8 12.3 Friday, November 10, 2017 Employee 1 8 14.3 Friday, November 17, 2017 Employee 1 8 13.1 Friday, November 3, 2017 Employee 1 8 11 Friday, October 13, 2017 Employee 1 8 11.1 Friday, October 20, 2017 Employee 1 8 12.5 Friday, October 6, 2017 Employee 1 8 11.5 Friday, September 29, 2017 Employee 1 8 16.8 Monday, December 11, 2017 Employee 1 8 14.8 Monday, December 4, 2017 Employee 1 8 12.1 Monday, February 19, 2018 Employee 1 8 11.4 Monday, February 5, 2018 Employee 1 8 6.4 Monday, February 5, 2018 Employee 1 8 7.7 Monday, January 15, 2018 Employee 1 8 7.1 Monday, January 22, 2018 Employee 1 8 9.3 Monday, January 29, 2018 Employee 2 8 7.3 Monday, January 8, 2018 Employee 2 8 10.6 Monday, March 21, 2016 Employee 2 8 7.3 Monday, May 14, 2018 Employee 2 8 7.9 Monday, May 21, 2018 Employee 2 8 12.9 Monday, November 13, 2017 Employee 2 8 14.6 Monday, November 20, 2017 Employee 2 8 15 Monday, November 27, 2017 Employee 2 8 13.3 Monday, November 6, 2017 Employee 2 8 12 Monday, October 16, 2017 Employee 2 8 11 Monday, October 2, 2017 Employee 2 8 6.5 Monday, October 23, 2017 Employee 2 8 11.8 Monday, October 9, 2017 Employee 2 8 17.1 Thursday, December 14, 2017 Employee 2 8 12.6 Thursday, December 21, 2017 Employee 2 8 7.2 Thursday, December 28, 2017 Employee 2 8 14.2 Thursday, December 7, 2017 Employee 2 8 7.7 Thursday, February 1, 2018 Employee 2 8 13.8 Thursday, February 22, 2018 Employee 2 8 12.9 Thursday, February 8, 2018 Employee 2 8 6.5 Thursday, January 11, 2018 Employee 2 8 7.1 Thursday, January 18, 2018 Employee 2 8 8.6 Thursday, January 25, 2018 Employee 2 8 6.2 Thursday, January 4, 2018 Employee 2 8 15 Thursday, March 1, 2018 Employee 2 8 10.1 Thursday, March 17, 2016 Employee 2 8 10.3 Thursday, March 24, 2016 Employee 2 8 10.9 Thursday, March 31, 2016 Employee 2 8 10.2 Thursday, May 10, 2018 Employee 2 8 7.4 Thursday, May 17, 2018 Employee 2 8 15.2 Thursday, November 30, 2017 Employee 2 8 12.8 Thursday, November 9, 2017 Employee 2 8 11.1 Thursday, October 12, 2017 Employee 2 8 11.8 Thursday, October 19, 2017 Employee 2 8 7.4 Thursday, October 19, 2017 Employee 2 8 7 Thursday, October 19, 2017 Employee 2 8 13.5 Thursday, October 5, 2017 Employee 2 8 12.1 Thursday, September 28, 2017 Employee 2 8 17.9 Tuesday, December 12, 2017 Employee 2 8 17 Tuesday, December 19, 2017 Employee 2 8 7.2 Tuesday, December 26, 2017 Employee 3 8 13.1 Tuesday, February 13, 2018 Employee 3 8 11.3 Tuesday, February 20, 2018 Employee 3 8 14.8 Tuesday, February 27, 2018 Employee 3 8 10.3 Tuesday, February 6, 2018 Employee 3 8 7.6 Tuesday, January 16, 2018 Employee 3 8 6.1 Tuesday, January 2, 2018 Employee 3 8 7.4 Tuesday, January 23, 2018 Employee 3 8 6.7 Tuesday, January 30, 2018 Employee 3 8 8.2 Tuesday, January 9, 2018 Employee 3 8 9.2 Tuesday, March 15, 2016 Employee 3 8 10.6 Tuesday, March 22, 2016 Employee 3 8 7.5 Tuesday, May 15, 2018 Employee 3 8 8.5 Tuesday, May 22, 2018 Employee 3 8 13.1 Tuesday, November 14, 2017 Employee 3 8 14.4 Tuesday, November 28, 2017 Employee 3 8 12.9 Tuesday, November 7, 2017 Employee 3 8 11.2 Tuesday, October 10, 2017 Employee 3 8 10.2 Tuesday, October 17, 2017 Employee 3 8 7.2 Tuesday, October 24, 2017 Employee 3 8 11.2 Tuesday, October 3, 2017 Employee 3 8 6.7 Wednesday, December 27, 2017 Employee 3 8 14.8 Wednesday, December 6, 2017 Employee 3 8 13.8 Wednesday, February 21, 2018 Employee 3 8 14.7 Wednesday, February 28, 2018 Employee 3 8 10.2 Wednesday, February 7, 2018 Employee 3 8 6.5 Wednesday, January 10, 2018 Employee 3 8 7.7 Wednesday, January 17, 2018 Employee 3 8 7.4 Wednesday, January 24, 2018 Employee 3 8 6.8 Wednesday, January 3, 2018 Employee 3 8 7.2 Wednesday, January 31, 2018 Employee 3 8 10.3 Wednesday, March 16, 2016 Employee 3 8 8.7 Wednesday, March 30, 2016 Employee 3 8 6.8 Wednesday, May 16, 2018 Employee 3 8 9.8 Wednesday, May 23, 2018 Employee 3 8 14.1 Wednesday, May 9, 2018 Employee 3 8 12.6 Wednesday, November 15, 2017 Employee 3 8 14 Wednesday, November 29, 2017 Employee 3 8 10.1 Wednesday, November 8, 2017 Employee 3 8 10.5 Wednesday, October 11, 2017 Employee 3 8 10.5 Wednesday, October 18, 2017 Employee 3 8 11.5 Wednesday, October 4, 2017 Employee 3

I am looking to get the result for sum of hours2 divided by hours1 to be 3.93

Senior Member

Re: Problem with summing a measure

Give this is a shot:

```Hours 2 / Hours 1 =
SUMX(
VALUES( Table1[Employee]),
CALCULATE(
DIVIDE( [Total Hours 2], [Total Hours 1] )
)
)```

Frequent Visitor

Re: Problem with summing a measure

I might be missing something easy here but may you take a look please?

Established Member

Re: Problem with summing a measure

The error is because you are referring naked columns, when Power BI expects an aggregation function wraped. You could just add a SUM around the 2 columns, or, if you already have a measure Hours2 Divided by Hours1, then you could just re-use it:

```Fixed Hours2 Divided by Hours1 =
SUMX ( VALUES ( Table2[Employee] ), [Hours2 Divided by Hours1] )```

Frequent Visitor

Re: Problem with summing a measure

Thank you both for your assistance, that worked!