topic Re: Calculate average from a table with snapshots in DAX Commands and Tips

Calculate average from a table with snapshots

pmonte — Fri, 16 Feb 2024 06:36:42 GMT

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?

Re: Calculate average from a table with snapshots

govindarajan_d — Thu, 15 Feb 2024 19:12:40 GMT

Hi pmonte,

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

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

Re: Calculate average from a table with snapshots

pmonte — Fri, 16 Feb 2024 06:35:44 GMT

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

Any ideas?

Re: Calculate average from a table with snapshots

govindarajan_d — Fri, 16 Feb 2024 06:55:52 GMT

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

Re: Calculate average from a table with snapshots

pmonte — Fri, 16 Feb 2024 15:17:07 GMT

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.

Re: Calculate average from a table with snapshots

pmonte — Fri, 16 Feb 2024 15:23:08 GMT

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

Re: Calculate average from a table with snapshots

govindarajan_d — Fri, 16 Feb 2024 17:17:00 GMT

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!