Forum Discussion

derekli1700's avatar
derekli1700
Helper III
2 years ago
Solved

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

 
Date of ActionCustomerVolumeTransactionAssets
13/01/2020Customer A              44,499.2             1,148               60
5/03/2020Customer B                 2,186.1                    16                  1
5/06/2021Customer A           145,243.4                 508             106
26/08/2021Customer B                     631.6                    16                  4
5/04/2022Customer C                 1,423.1                    32               12
26/06/2022Customer A                     758.7                    15                  2
31/03/2023Customer C              62,440.1                 214               58
4/08/2023Customer C                 2,245.7                    17                  1
15/08/2023Customer B           220,562.9                 854               63
28/11/2023Customer A                 1,669.1                    29                  8
6/03/2024Customer C              42,202.8                    58               12
23/05/2024Customer B              26,053.9                 103               29
25/07/2024Customer A              15,330.9                 287               51
 
 

This is a sample simplified dataset of the datasheet i'm using for my PowerBI dashboard.

I wish to learn how to create 3 visuals:

  1. One card showing the overall average period of time (in months) for customer activities before ceasing

  2. One card showing the average monthly customer volume

  3. Would also be nice to calculate the average transaction per asset in a card visual too - if possible
Need Help
Tutorial Requests

6 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    2 years ago

    what's the calculation logic? I don't think your request is just using average for the DAX.could you pls proivde the output?

    • derekli1700's avatar
      derekli1700
      Helper 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
        Super 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