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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
AnaJakic
New Member

Building sum over multiple rows

Hi everyone,

 

I am just starting to work with Power BI and I have a question concering the aggregation of a variable. 

 

Here is an tiny excerpt of my ABT that I use for my Power BI visualizations. 

 ABT.PNG

 

I would like to visualize a KPI that returns the sum of revenue for the respective Services (S1, S2, etc.). As the revenue in my table is displayed on the Service-level, the sum of revenue is 7.000 (3.000 for S 1 and 4.000 for S 2). In Tableau I would use the {include[service]:avg[revenue]}. 

 

No matter what I do, I got the sum of the complete column "Revenue", meaning that services are included several times in the revenue I would like to display. 

 

What should I do in Power BI to get the "right" number? 

 

Thank you very much for your help! 

 

Best

Ana

1 ACCEPTED SOLUTION
v-ljerr-msft
Microsoft Employee
Microsoft Employee

Hi @AnaJakic,

 

You should be able to use the formula below to create a measure to calculate the sum of revenue in your scenario. Smiley Happy

Measure =
SUMX (
    SUMMARIZE ( Table1, Table1[Service], "RevenueAVG", AVERAGE ( Table1[Revenue] ) ),
    [RevenueAVG]
)

Note: You'll need to replace 'Table1' with your real table name.

 

r1.PNG

 

Regards

View solution in original post

1 REPLY 1
v-ljerr-msft
Microsoft Employee
Microsoft Employee

Hi @AnaJakic,

 

You should be able to use the formula below to create a measure to calculate the sum of revenue in your scenario. Smiley Happy

Measure =
SUMX (
    SUMMARIZE ( Table1, Table1[Service], "RevenueAVG", AVERAGE ( Table1[Revenue] ) ),
    [RevenueAVG]
)

Note: You'll need to replace 'Table1' with your real table name.

 

r1.PNG

 

Regards

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

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