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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
water-guy-5
Helper III
Helper III

Calculating a sum of multiple locations

Hello,

I asked a question yesterday and have a little more information to provide!

So, my goal is to calculate the total reading/output of a specific city and compare it to what is required by law. I take the last 90 days of readings to calculate this.

Here's what I want: the average readings by specific equipment, and then the total of the averages (picture attached)

waterguy5_0-1654872694499.png

Right now, I have a measure that calculates the # of readings in the last 90 days, the sum of the readings, and it divides to find the total average.

Measure = 

Var AmountRecorded = SUM(Table[Value]) ---> This is just a column that has the readings
Var Readings = Table[NumOfReadings] ---> This is measure saying countrows that are greater than 0 and not blank
Var MaxDate = MAX(Table[Date])
Return
CALCULATE(
DIVIDE(AmountRecorded,Readings),
DATESBETWEEN(Table[Date], MaxDate, MaxDate - 90))


The measure above is essentially performing this...

waterguy5_1-1654872990829.png

 

It combines all the values and calculates a total average. Instead, I would want a measure that adds 250, 350, and 324 to give me a total "average" of 924.

Please let me know if I need to add any other info for help.

Thanks!



 








1 ACCEPTED SOLUTION
nvprasad
Solution Sage
Solution Sage

Hi,

 

The below formula should work for your requirment.

 

Result = SUMX(SUMMARIZE ( 'Table', 'Table'[Equipment] ),CALCULATE( AVERAGE('Table'[Reading])))
 
Also result should be 250 + 250 + 291 = 891 ( instead of 924)
 
Thank You,
 
Regards,
N  V Durga Prasad

View solution in original post

1 REPLY 1
nvprasad
Solution Sage
Solution Sage

Hi,

 

The below formula should work for your requirment.

 

Result = SUMX(SUMMARIZE ( 'Table', 'Table'[Equipment] ),CALCULATE( AVERAGE('Table'[Reading])))
 
Also result should be 250 + 250 + 291 = 891 ( instead of 924)
 
Thank You,
 
Regards,
N  V Durga Prasad

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.