Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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] )
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] )
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
Give this is a shot:
Hours 2 / Hours 1 = SUMX( VALUES( Table1[Employee]), CALCULATE( DIVIDE( [Total Hours 2], [Total Hours 1] ) ) )
I might be missing something easy here but may you take a look please?
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] )
Thank you both for your assistance, that worked!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |