Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

AVG for Positive values and with the Positive Value's Row Count

I am trying to Calculate the AVERAGE only for the NON NEGATIVE numbers but only with the Positive row count.

 

  1. If you look at the below data I want to calculate the AVG of MB Change Value % for Server A.
  2. Total number of rows for Server A is 16. If I do SUM(MB Change Value %)/16 then the AVG is 1.00 %
  3. But the row count of Positive MB Change Value %s for Server A is 8. We have to add only the positive values like SUM( +ve Values of MB Change Value %) / 8 is giving be average of 2.07.

 

Currently I am using the below DAX to achieve the AVG for each Server and Disk but no idea on how to achieve the above requirement.

 

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] )
    )
)

 

DATA:

 

Computer NameDiskMB Change ValueMB Change Value %Reported Date
Server AN:99691.275.73%26/04/2019 23:00
Server AN:87770.254.76%15/04/2019 23:00
Server AN:35602.582.05%12/04/2019 23:00
Server AN:28170.461.57%18/04/2019 23:00
Server AN:20950.671.20%31/05/2019 23:00
Server AN:9279.050.53%30/05/2019 23:00
Server AN:6724.050.39%09/04/2019 23:00
Server AN:5714.220.33%03/06/2019 23:00
Server AN:-186.55-0.01%08/04/2019 23:00
Server AN:-205.44-0.01%20/04/2019 23:00
Server AN:-338.53-0.02%20/05/2019 23:00
Server AN:-606.53-0.03%09/05/2019 23:00
Server AN:-653.07-0.04%10/05/2019 23:00
Server AN:-871.88-0.05%19/05/2019 23:00
Server AN:-2144.4-0.12%07/05/2019 23:00
Server AN:-3711.39-0.21%23/05/2019 23:00
Server BN:5410.140.31%21/04/2019 23:00
Server BN:4575.030.26%14/05/2019 23:00
Server BN:3802.640.22%22/04/2019 23:00
Server BN:2518.70.14%16/05/2019 23:00
Server BN:-113.65-0.01%17/05/2019 23:00
Server BN:-3777.58-0.22%08/05/2019 23:00
Server BN:-6354.5-0.36%07/04/2019 23:00
Server BN:-6643.87-0.39%29/05/2019 23:00
Server BN:-7112.02-0.41%18/05/2019 23:00
Server BN:-12412.3-0.72%11/05/2019 23:00
Server BN:-15902.99-0.91%24/05/2019 23:00
Server BN:-29395.69-1.72%28/05/2019 23:00
Server BN:-60113.89-3.45%02/05/2019 23:00
Server BN:-140230.27-8.06%01/06/2019 23:00
Server BN:-156203.53-8.97%05/05/2019 23:00

 

Need Help
Tips and Tricks
  • v-yuta-msft's avatar
    v-yuta-msft
    7 years ago

    Anonymous ,

     

    You may modify your measure using DAX like below and check if it can meet your requirement:

    Result =
CALCULATE (
    SUM ( 'Disk Space DW'[MB Change Value%] ),
    FILTER (
        'Disk Space DW',
        'Disk Space DW'[Computer Name] = EARLIER ( 'Disk Space DW'[Computer Name] )
            && 'Disk Space DW'[Disk] = EARLIER ( 'Disk Space DW'[Disk] )
            && 'Disk Space DW'[MB Change Value%] >= 0
    )
)
    / COUNTROWS (
        FILTER (
            'Disk Space DW',
            'Disk Space DW'[Computer Name] = EARLIER ( 'Disk Space DW'[Computer Name] )
                && 'Disk Space DW'[Disk] = EARLIER ( 'Disk Space DW'[Disk] )
                && 'Disk Space DW'[MB Change Value%] >= 0
        )
    )

    Community Support Team _ Jimmy Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

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

    Anonymous ,

     

    You may modify your measure using DAX like below and check if it can meet your requirement:

    Result =
CALCULATE (
    SUM ( 'Disk Space DW'[MB Change Value%] ),
    FILTER (
        'Disk Space DW',
        'Disk Space DW'[Computer Name] = EARLIER ( 'Disk Space DW'[Computer Name] )
            && 'Disk Space DW'[Disk] = EARLIER ( 'Disk Space DW'[Disk] )
            && 'Disk Space DW'[MB Change Value%] >= 0
    )
)
    / COUNTROWS (
        FILTER (
            'Disk Space DW',
            'Disk Space DW'[Computer Name] = EARLIER ( 'Disk Space DW'[Computer Name] )
                && 'Disk Space DW'[Disk] = EARLIER ( 'Disk Space DW'[Disk] )
                && 'Disk Space DW'[MB Change Value%] >= 0
        )
    )

    Community Support Team _ Jimmy Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.