Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
nkillia
Frequent Visitor

Problem with summing a measure

‎04-19-2019 02:48 PM

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. 

Labels:
Message 1 of 7
551 Views
0
1 ACCEPTED SOLUTION
AkhilAshok
Solution Sage
Solution Sage
In response to nkillia

‎04-26-2019 08:53 AM

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] )

 

View solution in original post

Message 6 of 7
463 Views
0
6 REPLIES 6
Anonymous
Not applicable

‎04-19-2019 05:10 PM

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]
)
Message 2 of 7
537 Views
0
nkillia
Frequent Visitor
In response to Anonymous

‎04-22-2019 07:32 AM

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

86.8Friday, December 29, 2017Employee 1
817.6Friday, December 8, 2017Employee 1
88.1Friday, February 2, 2018Employee 1
812.4Friday, February 9, 2018Employee 1
86.1Friday, January 12, 2018Employee 1
86.6Friday, January 19, 2018Employee 1
87.1Friday, January 26, 2018Employee 1
87.1Friday, January 5, 2018Employee 1
810.3Friday, March 18, 2016Employee 1
89.8Friday, March 25, 2016Employee 1
86.6Friday, May 11, 2018Employee 1
87.4Friday, May 18, 2018Employee 1
812.3Friday, November 10, 2017Employee 1
814.3Friday, November 17, 2017Employee 1
813.1Friday, November 3, 2017Employee 1
811Friday, October 13, 2017Employee 1
811.1Friday, October 20, 2017Employee 1
812.5Friday, October 6, 2017Employee 1
811.5Friday, September 29, 2017Employee 1
816.8Monday, December 11, 2017Employee 1
814.8Monday, December 4, 2017Employee 1
812.1Monday, February 19, 2018Employee 1
811.4Monday, February 5, 2018Employee 1
86.4Monday, February 5, 2018Employee 1
87.7Monday, January 15, 2018Employee 1
87.1Monday, January 22, 2018Employee 1
89.3Monday, January 29, 2018Employee 2
87.3Monday, January 8, 2018Employee 2
810.6Monday, March 21, 2016Employee 2
87.3Monday, May 14, 2018Employee 2
87.9Monday, May 21, 2018Employee 2
812.9Monday, November 13, 2017Employee 2
814.6Monday, November 20, 2017Employee 2
815Monday, November 27, 2017Employee 2
813.3Monday, November 6, 2017Employee 2
812Monday, October 16, 2017Employee 2
811Monday, October 2, 2017Employee 2
86.5Monday, October 23, 2017Employee 2
811.8Monday, October 9, 2017Employee 2
817.1Thursday, December 14, 2017Employee 2
812.6Thursday, December 21, 2017Employee 2
87.2Thursday, December 28, 2017Employee 2
814.2Thursday, December 7, 2017Employee 2
87.7Thursday, February 1, 2018Employee 2
813.8Thursday, February 22, 2018Employee 2
812.9Thursday, February 8, 2018Employee 2
86.5Thursday, January 11, 2018Employee 2
87.1Thursday, January 18, 2018Employee 2
88.6Thursday, January 25, 2018Employee 2
86.2Thursday, January 4, 2018Employee 2
815Thursday, March 1, 2018Employee 2
810.1Thursday, March 17, 2016Employee 2
810.3Thursday, March 24, 2016Employee 2
810.9Thursday, March 31, 2016Employee 2
810.2Thursday, May 10, 2018Employee 2
87.4Thursday, May 17, 2018Employee 2
815.2Thursday, November 30, 2017Employee 2
812.8Thursday, November 9, 2017Employee 2
811.1Thursday, October 12, 2017Employee 2
811.8Thursday, October 19, 2017Employee 2
87.4Thursday, October 19, 2017Employee 2
87Thursday, October 19, 2017Employee 2
813.5Thursday, October 5, 2017Employee 2
812.1Thursday, September 28, 2017Employee 2
817.9Tuesday, December 12, 2017Employee 2
817Tuesday, December 19, 2017Employee 2
87.2Tuesday, December 26, 2017Employee 3
813.1Tuesday, February 13, 2018Employee 3
811.3Tuesday, February 20, 2018Employee 3
814.8Tuesday, February 27, 2018Employee 3
810.3Tuesday, February 6, 2018Employee 3
87.6Tuesday, January 16, 2018Employee 3
86.1Tuesday, January 2, 2018Employee 3
87.4Tuesday, January 23, 2018Employee 3
86.7Tuesday, January 30, 2018Employee 3
88.2Tuesday, January 9, 2018Employee 3
89.2Tuesday, March 15, 2016Employee 3
810.6Tuesday, March 22, 2016Employee 3
87.5Tuesday, May 15, 2018Employee 3
88.5Tuesday, May 22, 2018Employee 3
813.1Tuesday, November 14, 2017Employee 3
814.4Tuesday, November 28, 2017Employee 3
812.9Tuesday, November 7, 2017Employee 3
811.2Tuesday, October 10, 2017Employee 3
810.2Tuesday, October 17, 2017Employee 3
87.2Tuesday, October 24, 2017Employee 3
811.2Tuesday, October 3, 2017Employee 3
86.7Wednesday, December 27, 2017Employee 3
814.8Wednesday, December 6, 2017Employee 3
813.8Wednesday, February 21, 2018Employee 3
814.7Wednesday, February 28, 2018Employee 3
810.2Wednesday, February 7, 2018Employee 3
86.5Wednesday, January 10, 2018Employee 3
87.7Wednesday, January 17, 2018Employee 3
87.4Wednesday, January 24, 2018Employee 3
86.8Wednesday, January 3, 2018Employee 3
87.2Wednesday, January 31, 2018Employee 3
810.3Wednesday, March 16, 2016Employee 3
88.7Wednesday, March 30, 2016Employee 3
86.8Wednesday, May 16, 2018Employee 3
89.8Wednesday, May 23, 2018Employee 3
814.1Wednesday, May 9, 2018Employee 3
812.6Wednesday, November 15, 2017Employee 3
814Wednesday, November 29, 2017Employee 3
810.1Wednesday, November 8, 2017Employee 3
810.5Wednesday, October 11, 2017Employee 3
810.5Wednesday, October 18, 2017Employee 3
811.5Wednesday, October 4, 2017Employee 3

EX1.pngEX2.png

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

Message 3 of 7
514 Views
0
Anonymous
Not applicable
In response to nkillia

‎04-24-2019 03:15 PM

Give this is a shot:

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

SUMX for Totals.png 

Message 4 of 7
484 Views
0
nkillia
Frequent Visitor
In response to Anonymous

‎04-26-2019 08:37 AM

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

Message 5 of 7
466 Views
0
AkhilAshok
Solution Sage
Solution Sage
In response to nkillia

‎04-26-2019 08:53 AM

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] )

 

Message 6 of 7
464 Views
0
nkillia
Frequent Visitor
In response to AkhilAshok

‎04-26-2019 09:30 AM

Thank you both for your assistance, that worked!

Message 7 of 7
456 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All