Forum Discussion

derekli1700's avatar
derekli1700
Icon for Helper III rankHelper III
2 years ago
Solved

Need help with getting average monthly activity duration and monthly volume of customer

  Date of Action Customer Volume Transaction Assets 13/01/2020 Customer A               44,499.2              1,148                60 5/03/2020 Customer B                  2,18...
Need Help
Tutorial Requests
derekli1700's avatar
derekli1700
Icon for Helper III rankHelper III
2 years ago

To clarify,
For visual 1. It should just show how long the average customer stays active for. As seen in the above data set, Customer C's activity spans from 05/4/22 to 06/3/24 which should show 23 months on the visual card. However, i would like the visual card number output to reflect the average activity duration for all customers. For my example: Customer A spans 54 months, B spans 50 months and C spans 23 months, so the visual card should show the average duration amount as (54+50+23)/3: 127 months 

 

For visual 3: It's just to find the average customer ratio for transaction and assets. Eg) Customer A in the example dataset has 1987 total transactions and 227 assets. Therefore, the visual card should show 8.75 transactions per asset (because 1987 / 227). But like in visual 1 - i would like the total average transaction per asset for the overall customers.

ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper User
2 years ago

derekli1700 

pls try this

 

Measure =
var tbl = ADDCOLUMNS( SUMMARIZE('Table','Table'[Customer],"mindate",min('Table'[Date of Action]),"maxdate",max('Table'[Date of Action])),"month", DATEDIFF([mindate],[maxdate],MONTH))
return sumx(tbl,[month])
 
 
Measure 2 = sumx(FILTER('Table','Table'[Customer]="Customer A"),'Table'[Transaction])/ sumx(FILTER('Table','Table'[Customer]="Customer A"),'Table'[Assets])
 
 
pls see the attachment below
 
  • derekli1700's avatar
    derekli1700
    Icon for Helper III rankHelper III
    2 years ago

    Hey thats really helpful thanks -

     

    For measure 1, my main data has a date heirachy which shows quarter, months, days etc as a dropdown. Does the measure still work for this?

    regarding measure 2, is there way for the measure to calculate the overall average customer transaction per assets (rather than one customer)? Thanks

    • ryan_mayu's avatar
      ryan_mayu
      Icon for Super User rankSuper User
      2 years ago

      for measure 1, not very clear about your request, you can have a try to see if it works.

      there is something wrong in measure 1, pls use averagex instead of sumx

       

      for measure 2, is the similar calculation logic you used for measure 1? 

       

      var tbl = ADDCOLUMNS( SUMMARIZE('Table','Table'[Customer],"mindate",min('Table'[Date of Action]),"maxdate",max('Table'[Date of Action])),"col",sum('Table'[Transaction])/sum('Table'[Assets]))
       
      return average(tbl,[col])