How to determine if sales has dropped off for a part number

[emma313823]

Hi All

I'm trying to determine if there is some way I can use this software to display some visuals on high running parts that have dropped off in sales.

I have a rather large pivot table that contains all of our sales information covering 2014 to present.  I'd like to find a way to flag part numbers for a customer that have significantly dropped off in sales.  We can come very close to 1 million rows of data as the year comes to an end and it can be hard to flag these type of things due to the sheer magnitude of the data.  By flagging the drop in sales, it can assist with bringing attention an issue to a customer - perhaps the sales engineer needs to visit the account, perhaps the part number has come to end of life the program is ended, or perhaps we can offer a replacement part generating more potential sales from the customer.

Does anyone have any thoughts on if BI Desktop software can assist with this project and if so - how would you begin?

Emma

[Anonymous]

HI @emma313823,

For your scenario, I think you can summary your records by grouping column, then calculate the diff between current records and previous records.

For example: summary records by custom id, date category.

Summary table =
SUMMARIZE (
    Table,
    Table[Year],
    Table[Month],
    Table[CustomerID],
    "Total Sales", SUM ( Table[Sales] )
)
 

Calcualte column:

MOM diff =
VAR ProviousDate =
    DATE ( ProviousDate[Year], ProviousDate[Month], 1 ) - 1
RETURN
    [Total Sales]
        - LOOKUPVALUE (
            'Summary table'[Total Sales],
            'Summary table'[Year], YEAR ( ProviousDate ),
            'Summary table'[Month], MONTH ( ProviousDate ),
            'Summary table'[CustomerID], [CustomerID]
        )

Reference:

SUMMARIZE Function (DAX)

Regards,

Xiaoxin Sheng

[san_jois]

Hi

You have data since 2014..

I am not sure if seasonality effect exists for the parts consumption being discussed.

However, we can use the approach of time period comparisons of parts consumption first Yearly, then monthly, Weekly and daily.

We can have a single measure for this comparison as Comparison with last year using SAMEPERIODLASTYEAR() DAX function.

You can then create the Slicers for Category of Part and Time period (Say year, month and Week)

You can use table to see the results in the PBI Canvas with table highlighting options for those which you want to be highlighted eg. Top 10%, or Top 10 etc.. or arrange the difference in descending order so that you can easily prioritise regionwise, categorywise etc.

Hope this clarifies..I could attempt to help more if you can share the PBIX sample file

[Ashish_Mathur]

Hi @emma313823,

How do you define "significantly dropped off in sales"?  Do you mean a decline in sales over a fixed % points over previus month/quarter?  Please specify.

[erik_tarnvik]

Hi @emma313823,

I am positive Power BI can address your challange. The  first step would be to get the data into Power BI using Power Query in a shape suitable for your analysis. You have not provided any information about what your data looks like, but I would probably try to use a quick measure such as Year over Year change as a first approach. If that is not sufficient you may need to learn some DAX to write the appropriate formulas. But there is no question in my mind that Power BI would be able to assist you in getting there.