cancel
Showing results for
Did you mean: 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! Announcements #### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023. #### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers! #### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture. Top Solution Authors
Top Kudoed Authors
Users online (5,064)