cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FPP
Frequent Visitor

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

I have the following table

DateKeyStatusAmount
Jan 1,2020A1open5
Jan 1,2020A1closed10
Jan 1,2020A1closed20
Jan 1,2020A2closed5
Jan 1,2020A2closed0
Jan 1,2020A3closed0
Jan 1,2020A4open40
Jan 1,2020A5closed50
Jan 1,2020A5closed-50
Jan 1,2020A5open30


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
AlexisOlson
Super User
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"
)

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
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"
)

It worked.  Thank you!

Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.