Total of Total Column

[Euto]

This is a sample data 

This is what I am getting = $1,300 with  "AVERAGE(DanSampleData[Rent])"

This is what I am expecting = $1,291

Please how do I contruct my dax ?

I have tried the following avgrent1 to avgrent6 with no luck.

MyTotal =

// Varibles

var Countofrows = CALCULATE(DISTINCTCOUNT(DanSampleData[Building]), ALL(DanSampleData))

var Agvrent = CALCULATE(AVERAGE(DanSampleData[Rent]), ALL(DanSampleData))

// Attempts

var avgrent1 = AVERAGE(DanSampleData[Rent])

var avgrent2 =

IF(HASONEVALUE(DanSampleData[Building]),

avgrent1,

SUMX(VALUES(DanSampleData[Building]), avgrent1)

)

var avgrent3 = CALCULATE(DIVIDE(Agvrent, Countofrows, 0), ALL(DanSampleData[Building]))

var avgrent4 =

CALCULATE(

AVERAGE(DanSampleData[Rent]),

ALLEXCEPT(DanSampleData, DanSampleData[Building], DanSampleData[Units])

)

var avgrent5 = AVERAGEX(DanSampleData, DanSampleData[Rent])

var avgrent6 = AVERAGEX(VALUES(DanSampleData[Building]), AVERAGE(DanSampleData[Rent]))

RETURN

avgrent6

[amitchandak]

@Euto , Try like

AVERAGEX(values(Table[Building]),AVERAGE(DanSampleData[Rent]))

[AlejandroAldama]

Instead of AVERAGE try using AVERAGEX

[Euto]

Thanks, but it didn't work.

[harshnathani]

Hi @Euto ,

Try this measure

avg rent =
SWITCH (
    TRUE (),
    NOT (
        ISINSCOPE ( 'Table'[Building] )
    )
        && NOT (
            ISINSCOPE ( 'Table'[Unit] )
        ), SUMX (
        VALUES ( 'Table'[Building] ),
        DIVIDE (
            CALCULATE (
                AVERAGE ( 'Table'[Price] )
            ),
            DISTINCTCOUNT ( 'Table'[Building] )
        )
    ),
    ISINSCOPE ( 'Table'[Unit] ), AVERAGE ( 'Table'[Price] ),
    ISINSCOPE ( 'Table'[Building] ), AVERAGE ( 'Table'[Price] )
)

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Euto · Accepted solution]

@harshnathani I am sorry, I may have to uncheck your solution as the solution. I will provide the solution and explain further below.

Solution

MyTotal = 
var vTable = VALUES('DanSampleData'[Building])
var bldCount = DISTINCTCOUNT('DanSampleData'[Building])
RETURN
SUMX (vTable,
    DIVIDE(
        CALCULATE(
            AVERAGE('DanSampleData'[Rent])),
            bldCount)
)

IMAGE OF SOLUTION APPLIED

Explanation
Your solution above did give me the expected result, but the construct of the DAX was overkill. It would not realy help someone  or someone else to use this solution.

I debugged the DAX you provided into several stages using variables.

Stage 1

MyTotal = 

var _Value1 = NOT (ISINSCOPE ( 'DanSampleData'[Building]))&& NOT (ISINSCOPE ( 'Table'[Unit] ))
var _Result1 = SUMX (VALUES ( 'DanSampleData'[Building] ),DIVIDE (CALCULATE (AVERAGE ( 'DanSampleData'[Rent] )),DISTINCTCOUNT ( 'DanSampleData'[Building] )))
var _Value2 = ISINSCOPE ( 'DanSampleData'[Rent] )
var _Result2 = AVERAGE ( 'DanSampleData'[Rent] )
var _Value3 = ISINSCOPE ( 'DanSampleData'[Building] )
var _Result3 = AVERAGE ( 'DanSampleData'[Rent] )

var Final =
SWITCH (TRUE ()
,_Value1, _Result1
,_Value2, _Result3
,_Value3, _Result3
)

RETURN
Final

In stage 1, with the Dax above, I realized that returning only the _Result1 give me the expected outcome, and I did not need to use the "ISINSCOPE" and "SWTITCH" funtions. 

Which means the result is 

MyTotal = SUMX (VALUES ( 'DanSampleData'[Building] ),DIVIDE (CALCULATE (AVERAGE ( 'DanSampleData'[Rent] )),DISTINCTCOUNT ( 'DanSampleData'[Building] )))

Stage 2

MyTotal = 
var vTable = VALUES('DanSampleData'[Building])
var bldCount = DISTINCTCOUNT('DanSampleData'[Building])
RETURN
SUMX (vTable,
    DIVIDE(
        CALCULATE(
            AVERAGE('DanSampleData'[Rent])),
            bldCount)
)

In Stage 2, with the dax above it's much easier to see what  is been calculated. Even with this I still have some observations and questions

- Observation

1. Using "CALCULATE(AVERAGE('DanSampleData'[Rent]))" in my RETURN works, but using "AVERAGE('DanSampleData'[Rent])" did not work.

2. Using "CALCULATE(AVERAGE('DanSampleData'[Rent]))" in variable like "avgTable = CALCULATE(AVERAGE('DanSampleData'[Rent]))" and then calling the variable in my return does not work. Which is why i did not use variable here.

- Questions

1. What is the different between "CALCULATE(AVERAGE('DanSampleData'[Rent]))" and "AVERAGE('DanSampleData'[Rent])" ? note that no filter is been applied to the Calculate function
2. Why does it not work when is use "CALCULATE(AVERAGE('DanSampleData'[Rent]))" in variable like "avgTable = CALCULATE(AVERAGE('DanSampleData'[Rent]))" ?

[Euto]

@harshnathani this worked like magic! Thank you!

I sasid "magic" lol because I am not able to understand what this measure is doing.

Can this be simplified? or further explained ?. I would appriciate. Thanks

[harshnathani]

Hi @Euto ,

Understanding of ISINSCOPE is the crux here.

https://www.youtube.com/watch?v=sSUCyps_1O4

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)