Calculate average from a table with snapshots

[pmonte]

I have a source table contains rows representing portfolio snapshots on specific dates, with columns: date, owner, category and number of items.
I need to create a table showing the owner and its average number of items in portfolio in a specific range of dates.

Owners are also grouped by geography (3 levels) in the table. So when I drill down to single owner I need to see avg of snapshots for the data range, if I drill up I need to display the SUM of the above mentioned averages.
The sheet includes sliders to set the range of dates, the owner(s) and category.
Can you guide me with the right DAX formulas?

[govindarajan_d]

Hi @pmonte,

Add owner in the table visual and try using this formula:

AverageItems = AVERAGEX(VALUES(Table[Portfolio]), [NumberOfItems])

[pmonte]

Thanks @govindarajan_d for the suggestion but, I do not know how, I forgot the key part of the question (now I'll try to edit it). 

Owners are also grouped by geography (3 levels) in the table. So when I drill down to single owner I need to see avg of snapshots for the data range, if I drill up I need to display the SUM of the above mentioned averages.

Any ideas? 

[govindarajan_d · Accepted solution]

Hi @pmonte,

I do not know the exact columns you are using, but please modify the below according to your requirements:

ItemMeasure =
VAR __Average = AVERAGEX(VALUES(Table[Portfolio]), [NumberOfItems])
VAR __Sum = SUM([NumberOfItems])
RETURN IF(INSCOPE(Table[GeographyLevel3]),__Average,__Sum)

Using INSCOPE we will be able to know at which level the current measure is being applied on and try to change the measure value accordingly

[pmonte]

Sounds great but SUM will sum all snapshots while I need to sum the average for each owner. 

Let's say that owner A (of Geo X) has two snapshots, 3 and 7, its AVG is 5. Then owner B (same Geo X) has two snapshots, 1 and 3 (AVG 2). If the table is showing owners I need to display A 5 and B 2. If the table shows Geo I need to display X 7 (while the sum of all snapshots would display 14.

[pmonte]

Sum divided by number of snapshots would work. Thanks for your help! 

[govindarajan_d]

Hi @pmonte ,

Can you please check this measure:

ItemMeasure = 
VAR __Average = AVERAGEX(VALUES('Table'[snapshot]),[SumOfItems])
VAR __Sum = SUMX(VALUES('Table'[Owner]),AVERAGE('Table'[Value]))
RETURN IF(ISINSCOPE('Table'[Owner]),__Average,__Sum)

I created some sample data based on your comment and you can see the result below that

Hope it helps!