cancel
Showing results for
Did you mean:
Frequent Visitor

## Average with a check against total non-zero and a distinct count

have the following table:

Note: I have a tran date that I did not inlcude below.  It is related to a calendar table via tran date.

 Cls Date Key 1 Key 2 Cat Status Amount A1 B1 A open 2 Jan 1,2020 A1 B2 A closed 10 Jan 1,2020 A1 B3 A closed 20 Jan 1,2020 A2 C1 A closed 5 Jan 3,2020 A2 C2 A closed -5 Jan 1,2020 A3 D1 B closed 0 Jan 2,2020 A4 E1 A closed 50 Jan 2,2020 A5 E2 A closed -50 A5 E3 B open 30 Jan 1,2020 A6 F1 A closed 40 Jan 2,2020 A6 F2 B closed -40

I need to crate an average that does this:

For each cls dte month do the following

1. Add up the amount and count unique Key 1
2. Only include Key 1 if the sum of amount is not 0 for Key 1 inside the month
 Cls Date Month Count of Distinct Key 1where Total Amount <> 0 Sum of Amount Note Jan, 2020 1 10+20=30 Counted A1Not countedA2 since 5-5=0,A3 since  0=0,A4, A5 since 50-50=0,A6 since 40-40=0

Average 30/1=30
Note 2 and 30 are not counted or added since they have no close date.

When doing it by Cat

 Cls Date Month Cat Count of Distinct Key 1where Total Amount <> 0 Sum of Amount Note Jan, 2020 A 2 10+20+40=70 Counted A1, A6 since it is 40 for Cat ANot countedA2 since 5-5=0,A4, A5 since 50-50= 0 Jan, 2020 B 1 -40 Counted  A6 since it is -40 for Cat BNot countedA3 since  0=0

Average Cat A  70/2=35
Average Cat B  -40/1=-40

Since the user can pick Cat via a filter visual on the screen.

How would I do this?

Fernando

1 ACCEPTED SOLUTION
Super User

@FPP  Try a meausre like

divide( sum(Table[Amount])

, Countx(filter(summarize(Table, Table[Key 1] , "_1", sum(Table[Amount])), [_1] > 0 ), [Key 1])

)

Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!
Super User

@FPP  Try a meausre like

divide( sum(Table[Amount])

, Countx(filter(summarize(Table, Table[Key 1] , "_1", sum(Table[Amount])), [_1] > 0 ), [Key 1])

)

Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

#### Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors