Measure sales performance per article per customer within a time span

[MaximilianR]

Hi everyone,

relatively new Power BI user here with a rather complicated question (I guess) and looking for help.

Szenario: We are company selling food products to different customers (B2B). Different products sell differently and customers buy in different frequencies from us (from a couple of days up to 8 weeks). We make a suggestion for the first set of products (40-70 different) but then customers can decide if the want to get rid of some or try some new one.

Data: We have tables from a ERP with rows containing each single article with customer info, quantity and sales amount.

Aim: We would like to have a way of measuring how well a product performs for each customer. Users should be able to select a time span and get the results for this time frame

Question 1: Do you have ideas for analyzing these data? I am sure that this question has been asked before in some form.

Question 2: I came up with an idea for a way of measuring this but it seems to be very complicated and I have no clue how to achieve that in Power BI. Here it is:

For each customer and each article, calculate sales amout on a daily basis. If the customer orders an article for 100 € and orders the same thing again after 20 days, sales would be 5 € per day for this article. As it is food and will go bad after a while, the max time span would be 50 days for example. If the customer does not ever order again, daily sales would be 2 € per day for 50 days and 0 € afterwards.

The Problem: I am not sure whether this can be done in Power BI, especially as I am thinking of calculated tables for each customer (~230) or article (~250), which would not be feasible...

Thanks for all incoming brainwaves!

[v-robertq-msft]

Hi, @MaximilianR 

According to your description, I can roughly understand what you want to get. But I find it hard to create such a large quantity of data based on your column details. Would you like to post some sample data in table form or pbix file(without sensitive data) and your expected result(like the chart you want to get and the correct measure value based on your sample data)?

Thanks very much!

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@MaximilianR ,For this one "We would like to have a way of measuring how well a product performs for each customer. Users should be able to select a time span and get the results for this time frame"

One of the way is to subcategory ranking  assuming sales is you measures 

Rankx(filter(Allselected(Table[Customer], Table[product]), [Customer] =Max(Table[Customer])),[Sales])

https://www.youtube.com/watch?v=DZb_6j6WuZ0

https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

[MaximilianR]

@amitchandak, thanks for the fast reply and the idea. However, I take it that this measure would only be able to rank different 'performaces' of customers within the selected time, but I would not be possible to show the performance of an article per custmoer over a whole year in a graph, right?

[amitchandak]

@MaximilianR , This will performance of each product for the customer.(Rank is the product inside a customer)

Use a clustered bar with Customer, Product. Switch off concatenate Labels. Sort on customer product. Filter on rank at visual level say 10 . You should get something like this. (Illustrative )