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

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.

     

    • pmonte's avatar
      pmonte
      Frequent Visitor
      2 years ago

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

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

        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!