Help with Sum of Averages

DAX DAX Calculations
1

Hi All,
I’m trying to get the sum of averages.

With one site selected it works

image
image1157×297 27.3 KB

With multiple sites, it does not work- I’m looking for the value to be (4059.29+227.77)= 4,287.06

image
image1167×330 32.5 KB

Here is a sample .pbix
Test PBIX.pbix (9.7 MB)

I know Summarize can be used, but I have about 6 MM rows, so I am concerned about the performance. Is there another way or is that the best practice?

I’ve also tried to create a column using “earlier”, but I did not get the result I was looking for.

Please let me know what you think.

2

Hi @DeanJ,

Give this a go.

Test_ProductLineBudget_Average v2a = 
VAR vTable = ALLSELECTED( SalesAggDept_Test[Site] )
RETURN

IF( ISFILTERED( SalesAggDept_Test[Site] ),
    CALCULATE(
        SUMX( vTable, [Test_ProductLineBudget_Average] ),
        REMOVEFILTERS( SalesAggDept_Test[DepartmentDescription] )
    ),
    [Test_ProductLineBudget_Average]
)

or (see the difference between the two depicted below)

Test_ProductLineBudget_Average v2b = 
VAR vTable = ALLSELECTED( SalesAggDept_Test[Site] )
RETURN

IF( ISFILTERED( SalesAggDept_Test[Site] ),
    SUMX( vTable, [Test_ProductLineBudget_Average] ),
    [Test_ProductLineBudget_Average]
)

and

Test_Budget Switch v2 = 
SWITCH( TRUE(),
    [Level] = "Department", [Test_DepartmentBudget],
    [Level] = "ProductLine", [Test_ProductLineBudget_Average v2]
)

.

image
image1072×192 22.5 KB

.
I hope this is helpful.

1 Like
3

Hi @DeanJ, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

4

Thanks @Melissa ! This is very close to what I need. There is one other condition though- it could be that no individual sites are selected. I would still need the sum of average per site. With those formulas, I think it is just the average.

One other requirement is that I need the Total to display.

5

So I ended up using Summarize and I think it worked. Now I just need to get the Totals to appear in the Matrix. I will create another post about that.

1 Like