Forum Discussion

GlitchedDuck's avatar
GlitchedDuck
Frequent Visitor
4 years ago

Average Count Per Day/Week/Month Card

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

Need Help

12 Replies

  • davehus's avatar
    davehus
    Memorable Member
    4 years ago

    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's avatar
      GlitchedDuck
      Frequent Visitor
      4 years ago

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

      • Tahreem24's avatar
        Tahreem24
        Super User
        4 years ago

        GlitchedDuck Wrap it with DAY function.

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

         

    • GlitchedDuck's avatar
      GlitchedDuck
      Frequent Visitor
      4 years ago
      Created_DateInvoice_Number
      24/03/20221
      24/03/20222
      24/03/20223
      24/03/20224
      24/03/20225
      24/03/20226
      23/03/20227
      23/03/20228
      23/03/20229
      22/03/202210
      22/03/202211
      22/03/202212
      22/03/202213
      22/03/202214
      21/03/202215
      21/03/202216
      21/03/202217
      21/03/202218
      21/03/202219
      • Tahreem24's avatar
        Tahreem24
        Super User
        4 years ago

        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.
         

         

  • johnt75's avatar
    johnt75
    Super User
    4 years ago
    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])
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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

  • americanomuerto's avatar
    americanomuerto
    Helper I
    3 years ago

    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's avatar
      americanomuerto
      Helper I
      3 years ago

      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