cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FPP
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 2CatStatusAmount
 A1B1Aopen2
Jan 1,2020A1B2Aclosed10
Jan 1,2020A1B3Aclosed20
Jan 1,2020A2C1Aclosed5
Jan 3,2020A2C2Aclosed-5
Jan 1,2020A3D1Bclosed0
Jan 2,2020A4E1Aclosed50

Jan 2,2020

A5E2Aclosed-50
 A5E3Bopen30
Jan 1,2020A6F1Aclosed40
Jan 2,2020A6F2Bclosed-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 MonthCount of Distinct Key 1
where Total Amount <> 0
Sum of AmountNote
Jan, 2020110+20=30Counted A1
Not counted
A2 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 1
where Total Amount <> 0
Sum of AmountNote
Jan, 2020A210+20+40=70Counted A1, A6 since it is 40 for Cat A
Not counted
A2 since 5-5=0,
A4, A5 since 50-50= 0

Jan, 2020B1-40Counted  A6 since it is -40 for Cat B
Not counted
A3 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
amitchandak
Super User
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])

)

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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 !!

View solution in original post

1 REPLY 1
amitchandak
Super User
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])

)

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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 !!

Helpful resources

Announcements
Microsoft Build 768x460.png

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_carousel_with_text (1).png

Charticulator Design Challenge

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

May UG Leader Call Carousel 768x460.png

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.

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

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