Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculate average for each main category

I have tried to calclate the Average Used Megabytes for each disk but please see below the output. I would like to calculate the Average Used Megabytes for Server A and Disk C = ? and similarly for o...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    HI, Anonymous 

    You could use EARLIER Function in your formula to get it.

    Result = 
CALCULATE (
    AVERAGE ( 'Disk Space DW'[Used Megabytes] ),
    FILTER (
        'Disk Space DW',
        'Disk Space DW'[Computer Name] = EARLIER ( 'Disk Space DW'[Computer Name] )
            && 'Disk Space DW'[Disk] = EARLIER ( 'Disk Space DW'[Disk] )
    )
)

    Result:

     

    Best Regards,

    Lin

v-lili6-msft's avatar
v-lili6-msft
Icon for Community Support rankCommunity Support
7 years ago

HI, Anonymous 

You could use EARLIER Function in your formula to get it.

Result = 
CALCULATE (
    AVERAGE ( 'Disk Space DW'[Used Megabytes] ),
    FILTER (
        'Disk Space DW',
        'Disk Space DW'[Computer Name] = EARLIER ( 'Disk Space DW'[Computer Name] )
            && 'Disk Space DW'[Disk] = EARLIER ( 'Disk Space DW'[Disk] )
    )
)

Result:

 

Best Regards,

Lin

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thanks for replying.. Is it possible to add a column to show Previous Day Used Megabytes for each date...?

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      7 years ago

      Hi,

      Assuming you want a calculated column formula solution, try this

      =CALCULTE(SUM(Data[Used Megabytes]),FILTER(Data,Data[Computer Name]=EARLIER(Data[Computer Name])&&Data[Reported Date]<EARLIER(Data[Reported Date])))

      Ensure that then entries in the Reported Date column are actual Date/Time entries.

      Hope this helps.