Average of values across dates; excluding nulls from the n

[Anonymous]

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'.  

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

The table is very simple, resembling the following.

Staff	Date	Invoice
A	7/15/2022	112185334
A	7/15/2022	112185893
A	7/20/2022	112497605
A	7/20/2022	112498195
B	7/20/2022	112498194
B	7/20/2022	112498194
B	7/13/2022	111973577
C	7/13/2022	111973579
C	7/13/2022	111974775
C	7/13/2022	111975113
D	7/15/2022	112195371
D	7/15/2022	112196425
D	7/15/2022	112196425

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!

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[TomMartens]

Hey @Anonymous 

this measure

Measure = 
DISTINCTCOUNT( 'Table'[Invoice] )

allows to create this matrix visual:

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

[Anonymous]

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 :

Staff	Average Invoice Count per Day
A	92
B	108

Thoughts?

[TomMartens]

Hey @Anonymous ,

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

[Anonymous]

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.

Staff	Day	Count of Invoice
A	1	28
A	2	1
A	3	1
A	5	176
A	6	136
A	7	166
A	8	123
A	10	2
A	11	43
A	12	122
A	13	117
A	14	136
A	15	112
A	16	8
A	17	4
A	18	123
A	19	143
A	20	148
A	21	157
A	22	105
A	23	5
A	24	4
A	25	165
A	26	153
A	27	113
A	28	136
A	29	112
A	30	38

[TomMartens]

Hey @Anonymous 

This measure

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

Allows to create this:

Regards,

Tom

[Anonymous]

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:

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.

[Anonymous]

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

Staff	Invoice	Date
B	112545573	July 22, 2022
B	112517414	July 22, 2022
B	112451694	July 24, 2022
B	112441246	July 24, 2022
B	112085543	July 22, 2022
B	111905906	July 22, 2022
B	111879724	July 22, 2022
B	111879721	July 22, 2022
B	111831391	July 25, 2022
B	111793965	July 25, 2022
B	111736169	July 22, 2022
B	111736076	July 25, 2022
B	111642521	July 22, 2022
B	111574609	July 22, 2022
B	111572749	July 22, 2022
B	111572748	July 22, 2022
B	111572726	July 22, 2022
B	111572723	July 22, 2022
B	111419751	July 22, 2022
B	111358082	July 22, 2022
B	111354357	July 22, 2022
B	103938792	July 25, 2022

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

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.

[Anonymous]

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 

Staff	Status	Invoice	Date Worked
A	Submit	123456	7/1/2022
A	Submit	123456	7/2/2022
A	Submit	123456	7/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]):

Staff	Status	Invoice	Date Worked
A	Submit	123456	7/3/2022

[Ashish_Mathur]

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

[Anonymous · Accepted solution]

Ashish,

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

Chase