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

[derekli1700]

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

[Ashish_Mathur · Accepted solution]

Hi,

PBi file attached.

[ryan_mayu]

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]

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]

@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]

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]

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])