Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nkillia
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

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

6 REPLIES 6
Anonymous
Not applicable

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

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

Anonymous
Not applicable

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 

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

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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