Get the average from Measure that's not 0 or blank

[JustDavid]

I have a simple data consist of 2 tables

I created measures ([Distinct BU Count], [PrevRefresh], [CurrentRefresh] and [Variance between Refresh]).

As you can see from the print screen, both measures [PrevRefresh] and [CurrentRefresh] are the result where it SUMS and divide [Distinct BU Count], and finally [Variance between Refresh] is the difference between the above mentioned measures.

My question now is, how can I calculate and get the average where [Variance between Refresh] is not 0 or blank? In other words, using the print screen below, I'd need to sum All the numbers that are not 0 and divide by 12

Thank you!

Note here that my [PrevRefresh] and [Current Refresh] measure takes from another measure:

Cost = SUM(tblFact[Amount])

CostWithInScopeCategory = 
SWITCH(TRUE(),
    AND(ISINSCOPE(tblCategory[Lv1_ShowDetail]), ISBLANK(VALUES(tblCategory[Lv1_ShowDetail]))) || 
    AND(ISINSCOPE(tblCategory[Lv2_ShowDetail]), ISBLANK(VALUES(tblCategory[Lv2_ShowDetail]))) || 
    AND(ISINSCOPE(tblCategory[Lv3_ShowDetail]), ISBLANK(VALUES(tblCategory[Lv3_ShowDetail]))) || 
    AND(ISINSCOPE(tblCategory[Lv4_ShowDetail]), ISBLANK(VALUES(tblCategory[Lv4_ShowDetail]))), BLANK(),
    [Cost]
)

Distinct BU Count = DISTINCTCOUNT(tblFact[BU ID]) <-- Note here that there's a red squiggly line in tblFact[BU ID]

CurrentRefresh = DIVIDE(CALCULATE([CostWithInScopeCategory], tblFact[File Timeline] = "Current"), [Distinct BU Count], BLANK())

PrevRefresh = DIVIDE(CALCULATE([CostWithInScopeCategory], tblFact[File Timeline] = "Previous"), [Distinct BU Count], BLANK())

Variance between Refresh = [PrevRefresh] - [CurrentRefresh]

Sample PBIX File 

[v-jiewu-msft]

Hi @JustDavid ,

I can't open the file link.

Based on the information, try using the following DAX formula to calculate.

Measure = 
VAR _variance = CALCULATE(SUMX(VALUES(tblFact[BU ID]),[Variance between Refresh]), FILTER(tblFact, tblFact[Variance between Refresh] <> 0 && NOT(ISBLANK([Variance between Refresh]))))
RETURN
DIVIDE(_variance, 12)

You can also view the following links to learn more information.

Solved: AVERAGE including blanks when want it to ignore bl... - Microsoft Fabric Community

Solved: Average excluding 0 - Microsoft Fabric Community

Solved: How to make a total sum for measure values within... - Microsoft Fabric Community

Best Regards,

Wisdom Wu

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

[JustDavid]

@v-jiewu-msft  thanks for replying...your last formula 

DIVIDE(_variance, 12)

Instead of hardcode 12, can it be dynamic? My thread writes 12 because, with the print screen, you can see that there's 12 unique counts of BU (although it's not in the print screen) where [Variance between refresh] <> 0

[v-jiewu-msft]

Hi,

Try using the following DAX formula.

Measure = 
VAR _variance = CALCULATE(SUMX(VALUES(tblFact[BU ID]),[Variance between Refresh]), FILTER(tblFact, tblFact[Variance between Refresh] <> 0 && NOT(ISBLANK([Variance between Refresh]))))
RETURN
DIVIDE(_variance, [Distinct BU Count])

Best Regards,

Wisdom Wu

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