Average Count Per Day/Week/Month Card

[GlitchedDuck]

Hi All,

I'm trying to create a few card visualization that shows average amount of invoices per Day/Week/Month.

In my data i have created_date, Invoice_Number and want to show the average in a card.

Is this possible?

Thanks

[americanomuerto]

I would like for there to be a solution for this topic as I have a similar issue. I had a very well working Measure until we passed to the new fiscal year and it all fell apart. 

Measure 1:

Irrigation Inspection Count = 
    CALCULATE(DISTINCTCOUNT(WCTS_INSPECTION[REQUEST_ID]),
WCTS_INSPECTION[INSPECTION_TYPE] = "IRR")

Measure 2:

Average Irrigation Audits per Week = 
VAR CurrentFiscalWeek = MAXX(FILTER(ALL(DimDate), DimDate[Date] = TODAY()), DimDate[Fiscal WeekNumber])
return
[Irrigation Inspection Count]/CurrentFiscalWeek

Once I started the new fiscal year, it divided the total inpection count (208) from the previous fiscal year by the current fiscal week number (2).

AverageX function is giving me an incorrect result of 4.43, rather than 4 (208/52).

Avg = AVERAGEX(VALUES(DimDate[Fiscal WeekNumber]),[Irrigation Inspection Count])

[americanomuerto]

Figured out the issue I was having. I just needed to make a slight modification to Measure 2

Average Irrigation Audits per Week = 
VAR CurrentFiscalWeek = MAX(DimDate[Fiscal WeekNumber])
return
[Irrigation Inspection Count]/CurrentFiscalWeek

[v-jayw-msft]

Hi @GlitchedDuck ,

Please check the following measures.

_day = calculate(average([invoice_number]),filter(allselected('table'),[create_date] = selectedvalue([create_date])))

_month = calculate(average([invoice_number]),filter(allselected('table'),month([create_date]) = month(selectedvalue([create_date]))))

_week = calculate(average([invoice_number]),filter(allselected('table'),weeknum([create_date]) = weeknum(selectedvalue([create_date]))))

Best Regards,

Jay

[johnt75]

Average per day = AVERAGEX( ADDCOLUMNS( VALUES(date_dim[full_date - No Time]), 
   "@val", CALCULATE([Invoice Count]) ), [@val])

Average per month = AVERAGEX( ADDCOLUMNS( SUMMARIZE( date_dim, date_dim[Year month]),
   "@val", CALCULATE([Invoice Count]) ), [@val])

[Tahreem24]

@GlitchedDuck, Better to share the dummy data here (not screen shot) to understand your need.

[GlitchedDuck]

Created_Date	Invoice_Number
24/03/2022	1
24/03/2022	2
24/03/2022	3
24/03/2022	4
24/03/2022	5
24/03/2022	6
23/03/2022	7
23/03/2022	8
23/03/2022	9
22/03/2022	10
22/03/2022	11
22/03/2022	12
22/03/2022	13
22/03/2022	14
21/03/2022	15
21/03/2022	16
21/03/2022	17
21/03/2022	18
21/03/2022	19

[Tahreem24]

@johnt75 Create one seperate Calendar table like below:

Calendar = CALENDAR(MIN(CreatedDate),MAX(CreatedDate))

Then create a different columns under this table:

MONTH = MONTH(Calendar[Date])

YEAR = YEAR(Calendar[Date])

DAY = DAY(Calendar[Date])

Then create a below Measures under your invoice table,

Avg Invoice per Month = CALCULATE(SUM(InvoiceTable[Invoice_Number]),ALLSELECTED('Calendar'[Month]))

Like wise just create for DAY and Year measure by chahing the ALLSELECTED funtion respectively.

[davehus]

Hi @GlitchedDuck ,

Assuming you are looking to discount invoices in your fact table.

Invoice Count = DISTINCTCOUNT(Table[InvoiceColumn]) 

Day = Averagex(Values(DateTable[Day]), [Invoice Count])

Week = Averagex(Values(DateTable[Week]), [Invoice Count])

Month = Averagex(Values(DateTable[Month]), [Invoice Count])

HTH

[GlitchedDuck]

Thist just seems to be giving me back the total number of invoices 

[davehus]

Have you a date table linked to your Invoice Header? The Date part of the measure I provided needs to be passed through the date table and not the fact table. Hence why you're getting everything. It provides the context for the average.

[GlitchedDuck]

I have a Date_Dim table thats joins to my Invoice_Table and it shows the same results. I can do an actual count by day with no issues, but when i add the average it gives me a total count of invoices

[Tahreem24]

@GlitchedDuck Wrap it with DAY function.

Day = Averagex(Values(DAY(DateTable[DateColumn])), [Invoice Count])