Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ChaseG
Frequent Visitor

Average of values across dates; excluding nulls from the n

Hello,

 

I need a measure that sums the values by category (in this business case, staff name) excluding dates where there are no values.

 

The purpose of this view is to calculate the average number of unique invoices that each staff works per day.  In the pic below, please see the highlighted empty/null cells as these would be excluded from the average's 'n'.  

 

ChaseG_0-1659385539761.png

From the above table, the measure for Staff "A" would result in 92 invoices per day.

 

The table is very simple, resembling the following.

StaffDateInvoice
A7/15/2022112185334
A7/15/2022112185893
A7/20/2022112497605
A7/20/2022112498195
B7/20/2022112498194
B7/20/2022112498194
B7/13/2022111973577
C7/13/2022111973579
C7/13/2022111974775
C7/13/2022111975113
D7/15/2022112195371
D7/15/2022112196425
D7/15/2022112196425

 

Volume is measured as DISTINCTCOUNT([Invoice ID]).

 

I've tried a few variations using DATEDIFF(), countrows(), etc., but nothing has worked.

 

Thank you for any help!

2 ACCEPTED SOLUTIONS

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Ashish,

 

Thank you, I looked this over and saw what you did.  I will try this with my business case, thanks again.

 

Chase

View solution in original post

12 REPLIES 12
TomMartens
Super User
Super User

Hey @ChaseG 

 

this measure

Measure = 
DISTINCTCOUNT( 'Table'[Invoice] )

allows to create this matrix visual:
image.png
Wondering if this is what you are looking for, if not - describe the expected result based on the sample data you have provided.

 

Regards,

Tom

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Tom,

 

Thanks for the quick reply and suggestion.  I'm currently using DISTINCTCOUNT as you suggest which created the grid vis I pasted in the initial string.  What I need to see is a simple grid :

 

StaffAverage Invoice Count per Day
A92
B108

 

Thoughts?

Hey @ChaseG ,

the sample data you provided does not contain 92 distinct invoices for staff(A).

Explain the expected result based on the sample data you provided.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Tom,

 

Yes, I understand - that was just a sample of a few rows of the table to illustrate the structure (columns/rows).  I tried to paste in my table.

 

StaffDayCount of Invoice
A128
A21
A31
A5176
A6136
A7166
A8123
A102
A1143
A12122
A13117
A14136
A15112
A168
A174
A18123
A19143
A20148
A21157
A22105
A235
A244
A25165
A26153
A27113
A28136
A29112
A3038

Hey @ChaseG 

This measure

Measure = 
AVERAGEX(
    'Table'
    , 'Table'[Count of Invoice]
)

Allows to create this:
image.png
Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Tom,

Thanks again for the prompt response and solution attempt however, this didn't work - it resulted in a colum with "1" on each row for Staff A,B,C,D...

Using a simple pivot and the count() function to calculate the number of days with Invoice Activity to demonstrate, the solution would resemble the value in column H:

ChaseG_2-1659654145360.png

 

Since I'm limited to what I can copy paste, I copied data for staff A,B, and C for dates 7/22-7/25 in another post.  The correct measure would result in values for A,B, and C of 55,7, and 55 respectively.

 

 

ChaseG
Frequent Visitor

I can't paste enough data for A,B, and C so what follows is the detail for B only:

StaffInvoiceDate
B112545573July 22, 2022
B112517414July 22, 2022
B112451694July 24, 2022
B112441246July 24, 2022
B112085543July 22, 2022
B111905906July 22, 2022
B111879724July 22, 2022
B111879721July 22, 2022
B111831391July 25, 2022
B111793965July 25, 2022
B111736169July 22, 2022
B111736076July 25, 2022
B111642521July 22, 2022
B111574609July 22, 2022
B111572749July 22, 2022
B111572748July 22, 2022
B111572726July 22, 2022
B111572723July 22, 2022
B111419751July 22, 2022
B111358082July 22, 2022
B111354357July 22, 2022
B103938792July 25, 2022

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

This worked! 

 

I did run into something unexpected.  Each invoice has a status that can be captured multiple times such that the invoice may have multiple rows, 1 for each status.  Example, invoice 1234567 may have a row with : SUBMIT, CREATE, and/or CLOSE.  How would I set this up so that the measure only counts a DISTINCT invoice per Staff?  If more than 1 staff worked an invoice, each should get credit for that invoice.

ChaseG
Frequent Visitor

Let me clarify further.  I've since simply used a filter to only pull the status of the invoice I want.  How would I prevent the same invoice from being 

StaffStatusInvoiceDate Worked
ASubmit1234567/1/2022
ASubmit1234567/2/2022
ASubmit1234567/3/2022

 

What I would like captured by the measure is an invoice captured ONCE across any date range where the date captured is the MAX([date worked]):

StaffStatusInvoiceDate Worked
ASubmit1234567/3/2022

I am confused.  Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Ashish,

 

Thank you, I looked this over and saw what you did.  I will try this with my business case, thanks again.

 

Chase

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.