Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 Name | Disk | Total Megabytes | Used Megabytes | Average of Used Megabytes |
Server A | C: | 75857.92 | 66733.4 | 66733.4 |
Server A | C: | 75868.16 | 66709.42 | 66709.42 |
Server A | C: | 75868.16 | 66711.86 | 66711.86 |
Server A | C: | 75868.16 | 66720.98 | 66720.98 |
Server A | C: | 75868.16 | 66728.63 | 66728.63 |
Server A | C: | 75878.4 | 66740.71 | 66740.71 |
Server A | C: | 75878.4 | 66755.84 | 66755.84 |
Server B | C: | 75868.16 | 16626.96 | 16626.96 |
Server B | C: | 75868.16 | 16629.83 | 16629.83 |
Server B | C: | 75868.16 | 26687.31 | 26687.31 |
Server B | C: | 75868.16 | 36690.27 | 36690.27 |
Server B | C: | 75868.16 | 40703.07 | 40703.07 |
Server B | C: | 75878.4 | 26687.31 | 26687.31 |
Solved! Go to Solution.
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
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.
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
Thanks for replying.. Is it possible to add a column to show Previous Day Used Megabytes for each date...?
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |