cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
josiasbr Frequent Visitor
Frequent Visitor

Show Average instead of Sum in TOTALS?

Hello everyone,

 
please I need your help! Below I have a table which shows 3 different services per month: Assembly, Delivery and Reparation. All of these services, have their own Prices, Quantities and Revenues, which are differentiated between "Actual" and "Budget" (what I actually have v/s what I should had)
 
I need to generate a column called "Total" which gives me the AVERAGE PRICE for each service (Actual and Budget Price) and also the SUM of the Quantity and Revenue of each service (also Actual and Budget), as you see in the table below 
 
Here you can notice that the figures for Quantity and Revenue are right, BUT as you see, the program is suming up the values of the Prices, which is wrong because I need the AVERAGE of them (I made these column just by activating the "Subtotal" option Power BI gives you when elaborating the matrix. ---> The numbers in yellow are wrong
 
Is there any form to do this???? I thought maybe with an IF Measure, but I don´t know what I´m doing wrong, because it doesn´t work.
 
Thank you very much in advance.

pOKaN4G

(Basically for PRICE I want the AVERAGE, but for the other 2 measures Quantity and Revenue, I want them to stay as SUM). Is that possible?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Show Average instead of Sum in TOTALS?

Hi @josiasbr ,

You can try to use following measure if it suitable for your requirement:

Measure =
IF (
    ISINSCOPE ( Table1[Month] ) && ISINSCOPE ( Table1[Version] ),
    SUM ( Table1[Value] ),
    AVERAGE ( Table1[Value] )
)

7.png

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

Re: Show Average instead of Sum in TOTALS?

HI @josiasbr ,

You can add if statement to check current row content level and write a formula to summarize total level.
If you are confused on coding formula, can you please share some sample data for test?

Measure Totals, The Final Word

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
josiasbr Frequent Visitor
Frequent Visitor

Re: Show Average instead of Sum in TOTALS?

Thank you for your answer! 

 

My data is this :

 

Service

Version

Month

Month Nr

Attribute

Value

Delivery

Actual

Jan

1

Quantity

332

Delivery

Actual

Jan

1

Price

728,51

Delivery

Actual

Jan

1

Revenue

242

Delivery

Budget

Jan

1

Quantity

347

Delivery

Budget

Jan

1

Price

819,5

Delivery

Budget

Jan

1

Revenue

284

Delivery

Actual

Feb

2

Quantity

317

Delivery

Actual

Feb

2

Price

740,22

Delivery

Actual

Feb

2

Revenue

235

Delivery

Budget

Feb

2

Quantity

330

Delivery

Budget

Feb

2

Price

819,5

Delivery

Budget

Feb

2

Revenue

270

Delivery

Actual

Mar

3

Quantity

296

Delivery

Actual

Mar

3

Price

776,09

Delivery

Actual

Mar

3

Revenue

230

Delivery

Budget

Mar

3

Quantity

393

Delivery

Budget

Mar

3

Price

819,5

Delivery

Budget

Mar

3

Revenue

322

Delivery

Actual

Apr

4

Quantity

294

Delivery

Actual

Apr

4

Price

766,9

Delivery

Actual

Apr

4

Revenue

225

Delivery

Budget

Apr

4

Quantity

339

Delivery

Budget

Apr

4

Price

819,5

Delivery

Budget

Apr

4

Revenue

278

Assembly

Actual

Jan

1

Quantity

1122

Assembly

Actual

Jan

1

Price

247,85

Assembly

Actual

Jan

1

Revenue

278

Assembly

Budget

Jan

1

Quantity

1075

Assembly

Budget

Jan

1

Price

256,29

Assembly

Budget

Jan

1

Revenue

276

Assembly

Actual

Feb

2

Quantity

1009

Assembly

Actual

Feb

2

Price

248,06

Assembly

Actual

Feb

2

Revenue

250

Assembly

Budget

Feb

2

Quantity

1000

Assembly

Budget

Feb

2

Price

256,29

Assembly

Budget

Feb

2

Revenue

256

Assembly

Actual

Mar

3

Quantity

1032

Assembly

Actual

Mar

3

Price

248,58

Assembly

Actual

Mar

3

Revenue

257

Assembly

Budget

Mar

3

Quantity

1035

Assembly

Budget

Mar

3

Price

256,29

Assembly

Budget

Mar

3

Revenue

265

Assembly

Actual

Apr

4

Quantity

931

Assembly

Actual

Apr

4

Price

248,83

Assembly

Actual

Apr

4

Revenue

232

Assembly

Budget

Apr

4

Quantity

992

Assembly

Budget

Apr

4

Price

256,29

Assembly

Budget

Apr

4

Revenue

254

Reparation

Actual

Jan

1

Quantity

328

Reparation

Actual

Jan

1

Price

160,91

Reparation

Actual

Jan

1

Revenue

53

Reparation

Budget

Jan

1

Quantity

319

Reparation

Budget

Jan

1

Price

192,41

Reparation

Budget

Jan

1

Revenue

61

Reparation

Actual

Feb

2

Quantity

296

Reparation

Actual

Feb

2

Price

148,66

Reparation

Actual

Feb

2

Revenue

44

Reparation

Budget

Feb

2

Quantity

298

Reparation

Budget

Feb

2

Price

192,41

Reparation

Budget

Feb

2

Revenue

57

Reparation

Actual

Mar

3

Quantity

292

Reparation

Actual

Mar

3

Price

145,9

Reparation

Actual

Mar

3

Revenue

43

Reparation

Budget

Mar

3

Quantity

320

Reparation

Budget

Mar

3

Price

192,41

Reparation

Budget

Mar

3

Revenue

62

Reparation

Actual

Apr

4

Quantity

298

Reparation

Actual

Apr

4

Price

147,54

Reparation

Actual

Apr

4

Revenue

44

Reparation

Budget

Apr

4

Quantity

298

Reparation

Budget

Apr

4

Price

192,41

Reparation

Budget

Apr

4

Revenue

57

 

So for every month, I have budget vs Actual for the 3 measures Quantity, Price and Revenue, and for the services "delivery", "assembly" and "reparation"

 

As I showed in the table above, I would like to see Qty and Revenue as a sum of the 4 months, but in Price I would like to see an average.

What exact formula would I need to type to achieve this?

Community Support Team
Community Support Team

Re: Show Average instead of Sum in TOTALS?

Hi @josiasbr ,

You can try to use following measure if it suitable for your requirement:

Measure =
IF (
    ISINSCOPE ( Table1[Month] ) && ISINSCOPE ( Table1[Version] ),
    SUM ( Table1[Value] ),
    AVERAGE ( Table1[Value] )
)

7.png

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 49 members 875 guests
Please welcome our newest community members: