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

[Anonymous]

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 Name	Disk	MB Change Value	MB Change Value %	Reported Date
Server A	N:	99691.27	5.73%	26/04/2019 23:00
Server A	N:	87770.25	4.76%	15/04/2019 23:00
Server A	N:	35602.58	2.05%	12/04/2019 23:00
Server A	N:	28170.46	1.57%	18/04/2019 23:00
Server A	N:	20950.67	1.20%	31/05/2019 23:00
Server A	N:	9279.05	0.53%	30/05/2019 23:00
Server A	N:	6724.05	0.39%	09/04/2019 23:00
Server A	N:	5714.22	0.33%	03/06/2019 23:00
Server A	N:	-186.55	-0.01%	08/04/2019 23:00
Server A	N:	-205.44	-0.01%	20/04/2019 23:00
Server A	N:	-338.53	-0.02%	20/05/2019 23:00
Server A	N:	-606.53	-0.03%	09/05/2019 23:00
Server A	N:	-653.07	-0.04%	10/05/2019 23:00
Server A	N:	-871.88	-0.05%	19/05/2019 23:00
Server A	N:	-2144.4	-0.12%	07/05/2019 23:00
Server A	N:	-3711.39	-0.21%	23/05/2019 23:00
Server B	N:	5410.14	0.31%	21/04/2019 23:00
Server B	N:	4575.03	0.26%	14/05/2019 23:00
Server B	N:	3802.64	0.22%	22/04/2019 23:00
Server B	N:	2518.7	0.14%	16/05/2019 23:00
Server B	N:	-113.65	-0.01%	17/05/2019 23:00
Server B	N:	-3777.58	-0.22%	08/05/2019 23:00
Server B	N:	-6354.5	-0.36%	07/04/2019 23:00
Server B	N:	-6643.87	-0.39%	29/05/2019 23:00
Server B	N:	-7112.02	-0.41%	18/05/2019 23:00
Server B	N:	-12412.3	-0.72%	11/05/2019 23:00
Server B	N:	-15902.99	-0.91%	24/05/2019 23:00
Server B	N:	-29395.69	-1.72%	28/05/2019 23:00
Server B	N:	-60113.89	-3.45%	02/05/2019 23:00
Server B	N:	-140230.27	-8.06%	01/06/2019 23:00
Server B	N:	-156203.53	-8.97%	05/05/2019 23:00

[v-yuta-msft · Accepted solution]

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