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 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'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

6 Replies

  • 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