cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## 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"
)``````
Helper I

It worked.  Thank you!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.