Show Average instead of Sum in TOTALS?

[josiasbr]

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.

(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?

[Anonymous]

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

[josiasbr]

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?

[Anonymous · Accepted solution]

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

Regards,

Xiaoxin Sheng