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 other servers and disks. Please help.

 

Computer NameDiskTotal MegabytesUsed MegabytesAverage of Used Megabytes
Server AC:75857.9266733.466733.4
Server AC:75868.1666709.4266709.42
Server AC:75868.1666711.8666711.86
Server AC:75868.1666720.9866720.98
Server AC:75868.1666728.6366728.63
Server AC:75878.466740.7166740.71
Server AC:75878.466755.8466755.84
Server BC:75868.1616626.9616626.96
Server BC:75868.1616629.8316629.83
Server BC:75868.1626687.3126687.31
Server BC:75868.1636690.2736690.27
Server BC:75868.1640703.0740703.07
Server BC:75878.426687.3126687.31

 

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

4 Replies

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

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

    Hi,

    Why do you want to write a calculated column formula.  To your Table/Matrix visual, drag Computer Name and Disk Fields and write this measure

    Measure1 = AVERAGE(Data[Used Megabytes])

    Hope this helps.