Identify and then track a type of customer over time

[RogierBI]

Hi,

I'd like to identify a certain type of customer and then plot how much they spend on average over time.

In my example I have people that sometimes buy berries, but also buy other types of fruit.

I've managed to create a list of Berrie Buyers (and can even filter them to get Regular berrie buyers).

However, when I try to calculate my average it gives the wrong answer.

I suspect this is because my list of Berrie Buyers doesn't remain constant over time.

Any idea how I can "mark" my buyers and then track their average spend over time?

If a Berrie Buyer buys apples or oranges I also want that data to show up in my graph.

So, if someone is identified as Berrie Buyer (in my 2 year data), I want to see everything he's bought over time.

Kind regards,

Rogier

AverageSpendonFruitbyBerrieBuyers = 
VAR AllBerrieBuyers = ADDCOLUMNS(FILTER(Data;Data[Products]="Berries");"CountBerrie";1)
VAR BerrieBuyers = GROUPBY(AllBerrieBuyers;[Buyer];"FinalCount";SUMX(CURRENTGROUP();[CountBerrie]))
VAR RegularBerrieBuyers = FILTER(BerrieBuyers;[FinalCount]>0)
RETURN
CALCULATE(DIVIDE(SUM(Data[Spend]);COUNTROWS(RegularBerrieBuyers);FILTER(RevitUsers;[FinalCount]>0))

[Seward12533]

Can you post a sample workbook with representative data? Need to upload to file sharing service like DropBox, One Drive, Google Drive or whatever, share and post a link.

Also when uing Add Columns, Summarize etc I find it it useful to let PowerBI actually build the table. You can then add columns via the menus, link to other table etc. and more importatly visualize what is going on.

To do this use "New Table" from the Modeling Tab on the report canvas.  And enter the Dax

All Berry Buyers Table = ADDCOLUMNS(FILTER(Data;Data[Products]="Berries");"CountBerrie";1)

etc..

But honesly from your describing I don't think even need the transient tables but its hard to tell without more details about your data.

[RogierBI]

Hi,

Here is an Excel with some sample data: Sample BerrieBuyers

The source data contains 4 buyers (Jake, Erin, Brad and Paul).

I'd like to identify the BerrieBuyers (Erin and Brad) and plot their average revenue over time.

The result as a table and as a graph would look like below.

BerrieBuyers	January	February	March	April	May	June	July	August
Erin	€       6,00	€       5,00	€       8,00	€       2,00	€       7,00	€       8,00	€       7,00	€       5,00
Brad	€       6,00	€       5,00	€       3,00	€       2,00	€       7,00	€       5,00	€       7,00	€       5,00
Total	€    12,00	€    10,00	€    11,00	€       4,00	€    14,00	€    13,00	€    14,00	€    10,00
Average Rev	€       6,00	€       5,00	€       5,50	€       2,00	€       7,00	€       6,50	€       7,00	€       5,00

 

[v-yuta-msft]

Hi RogierBI,

To achieve your requirement, create a calculate column to achieve the average value as below:

Average = CALCULATE(SUM(Table1[Revenue]) / 2, FILTER(Table1, MONTH(Table1[Date]) = MONTH(EARLIER(Table1[Date])) && (Table1[Buyer] = "Erin" || Table1[Buyer] = "Brad")))

Then you can create a line chart which reproduce your chart in excel.

 

Regards,

Jimmy Tao 

[Seward12533]

Jimmy, I think the example she provided was representive. I’m assuming those actual data has many more names and since the buyers have to be hard coded will needs to be changed if the names ever changed. She also had a requirement to show the table. I feel the solution I proposed is more flexible and scalable. And the user input to select the “buyers” could be replaced with a lookup table. I also choose which to show a combo chart that included the average line to demonstrate the power of PowerBI.

[RogierBI]

Thanks for the advice! I very much like the tips and tricks you provided @Seward12533 and have started doing some of the Tutorials. My PowerBI skills are limited but I've become very enhusiastic about the possibilities and am eager to learn more.

The solution of Jimmy is indeed not the way I'd like to do it, because of the need for hard coding (my list has thousands of buyers).

My goal is to have PowerBI identify the people that buy berries and then automatically create the table and graph of everything (not only berries) they've bought over time. I've managed to do this in Excel, but not yet in PowerBI.

The @Seward12533 solution also does not provide this possibility. I still have to identify the Berrie Buyers myself (Brad and Erin) and then manually select them to create the table and graph. Or am I missing something? I do like the possibility of selecting different products, but don't want to have to identify and select the buyers manually.

Kind regards, Rogier

[Seward12533]

Thanks, that is not too difficult. You can either to it with visual filters or in DAX. See this realted post.

https://community.powerbi.com/t5/Desktop/One-page-have-different-slicers-filter-different-tables/m-p...

Here is sample using DAX https://1drv.ms/u/s!AuCIkLeqFmlhhJoSaNg6AMjIQC8Sgw

Look at the tab labled Dynamic. 

The Top Table lists only Berry Buyers. If you select any of the buyers the table below and the graphs show all sales for that buyer.

[RogierBI]

Hi @Seward12533

Sorry, but this is not exactly what I meant.

Although the Table and the graphs look exactly the way I had them in mind by the way, identifying Brad and Erin and then selecting them is still a manual job.

The dashboard should automatically only list people that have bought berries (that would be Brad and Erin). The other people (Jake and Paul) should be filtered out automatically.

My goal is to create a dashboard based on identifying people that have bought a certain product and then showing their purchase history automatically. No manual selection should be needed.

[Seward12533]

Its easy enough a fairly simple extension of what I built for you which was intented to show you logic and approach.  If I get time I will try to build it for you but please dont' take this wth wrong way, there is a limit to the amount of free consulting you should expect us to do for you and you need to invest in learing how PowerBI works or hire someone who does.

[RogierBI · Accepted solution]

Hi @Seward12533. No worries, I appreciate all the help I get (and have gotten so far). 

I won't expect too much, which is fine. I've already gotten more then I had hoped for.

I'm doing some of the online courses now, so maybe I'll be able to solve it myself soon.

If that's the case I'll post my solution here for others to profit from.

Cheers!

[Seward12533]

Hi, the table stucture you want is not easy to do in PowerBI showing details and then totals and averages below. I used separate matrix tables, you could write sepearate measures for each and use the same display on Rows trick I used to to build show the totals and averages.  (Tip you would use calculate). The paradigm of PowerBI and PowerPivot are very different than Regular Excel.  I woudl strongly recommend you take time time to learn the basics with PowerBI (lots of great content out there including links right from inside powerbi.

That said I did build a demo with your data including a few advanced tricks to give you ideas (like Dynamic Titles, creating the date table dynamically via DAX from your data, tweaking formatting options for slicers to get you buttons). Tho the data you shared didn't match the snip you posted.