Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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])
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
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
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])
@GlitchedDuck, Better to share the dummy data here (not screen shot) to understand your need.
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 |
@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,
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
Thist just seems to be giving me back the total number of invoices
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.
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
@GlitchedDuck Wrap it with DAY function.
Day = Averagex(Values(DAY(DateTable[DateColumn])), [Invoice Count])