Forum Discussion

pmonte's avatar
pmonte
Frequent Visitor
2 years ago
Solved

Calculate average from a table with snapshots

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 av...
  • govindarajan_d's avatar
    govindarajan_d
    2 years ago

    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

govindarajan_d's avatar
govindarajan_d
Icon for Super User rankSuper User
2 years ago

Hi pmonte,

 

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

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

 

  • pmonte's avatar
    pmonte
    Frequent Visitor
    2 years ago

    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's avatar
      govindarajan_d
      Icon for Super User rankSuper User
      2 years ago

      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's avatar
        pmonte
        Frequent Visitor
        2 years ago

        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.