cancel
Showing results for
Search instead for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

## Different Average - filtered items and total for a group not zero

I have the following table

 Date Key Status Amount Jan 1,2020 A1 open 5 Jan 1,2020 A1 closed 10 Jan 1,2020 A1 closed 20 Jan 1,2020 A2 closed 5 Jan 1,2020 A2 closed 0 Jan 1,2020 A3 closed 0 Jan 1,2020 A4 open 40 Jan 1,2020 A5 closed 50 Jan 1,2020 A5 closed -50 Jan 1,2020 A5 open 30

I want the following:

numerator= SUM (Amount) where Status = 'closed' AND Amount <> 0 (Not a big deal to add the zero, since it doesn't change the SUM)
denominator= COUNT of DISTINCT(Key) where Status = 'closed' AND SUM(Amount grouped by the key) <> 0
Ans: numerator/denominator

So for above:
numerator=10+20+5+0+50-50=35
denominator=2

Ans = 35/2 = 17.5

OR

numerator=10+20+5=35
denominator=2

Ans = 35/2 = 17.5

OR

numerator=30+5=35
denominator=2

Ans = 35/2 = 17.5

How can I do this in DAX?

1 ACCEPTED SOLUTION
Super User

I think this should do the trick:

``````DifferentAverage =
CALCULATE (
DIVIDE (
SUM ( Table1[Amount] ),
COUNTROWS (
FILTER (
SUMMARIZE ( Table1, Table1[Key], "KeySum", SUM ( Table1[Amount] ) ),
[KeySum] <> 0
)
)
),
Table1[Status] = "closed"
)``````
2 REPLIES 2
Super User

I think this should do the trick:

``````DifferentAverage =
CALCULATE (
DIVIDE (
SUM ( Table1[Amount] ),
COUNTROWS (
FILTER (
SUMMARIZE ( Table1, Table1[Key], "KeySum", SUM ( Table1[Amount] ) ),
[KeySum] <> 0
)
)
),
Table1[Status] = "closed"
)``````
Frequent Visitor

It worked.  Thank you!

## Helpful resources

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors