How to create measure for purchase frequency

[Anonymous]

Hi Guys,

Hope you are doing well and safe!!

I want to create a measure for dynamic purchase frequency table in power bi through which I can find out count of re-purchase done by every customer in a month. Also in the same table, some of the entries have zero purchase value which i want to exclude from count.

For Example (Customer-A purchase time in a month is 3, Customer-B purchase time in a month is 5 etc.)

Also i want to demonstrate the same over line chart in which X-axis is Frequency and Y-axis is Purchase Volume.

Good To have: if it possible that if i select a month from a slicer and frequency will sort out according month to month scenario (for example if you see the above photo in the table frequency 5 and 7 is 0).

Note: This visual is based on user month slicer.

Thanks in advance 🙂

[Thejeswar]

Hi @Anonymous ,

More information in the form of sample data might be required for this

For example, you say Customer A purchases 3 times, and Customer B purchases 5 times. But we can't see the customer details in the sample data that you have shown.

Input Sample data and Output Sample data might be required for easier analysis

[Anonymous]

Hi @Thejeswar ,

Please refer this link: https://drive.google.com/drive/folders/1X1b5AJRLRH5OEjzhanjsO9CjfYcPmDAL?usp=sharing

In this folder you will find the file in which raw data have been placed in excel format "Data Sheet".

And above provided screenshot is the output sample in excel file... which is also again available in the same file.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.  The first table and the graph are what you want.  The second table is for verifying the results of first table.  Your and my answers will not match because i have excluded customers which had a value of 0.

Hope this helps.

[Anonymous]

Hi @Ashish_Mathur ,

Thanks for your revert 🙂

The solution is perfectly working for me as per dummy data. now let me apply the same in my actual things.

One query for you (if possible for you):

Can you please breif me about the below measure which you have created in your pbix file.

like what is the use of using "ABCD" here and step by step breif would be appreciable

and i guess the same you have applied for purchase vol. as well.

[Ashish_Mathur]

You are welcome.

[Anonymous]

Hi @Ashish_Mathur ,

can you please breif the meaure you have created for "Number of customer purchase frequency" like what is the use of "ABCD" in the same and step by step breif will be appreciable.

[Ashish_Mathur]

Hi,

Please read up on dynamic segmentation here.