Total Sales Lost from Underperforming Customers

[Anonymous]

Hello All, 

I'd like to show the SUM of the total sales lost by customers who are performing worse than last year. I have a YoY calculated measure, that I thought you could filter on, however it seems that you cannot have a page filter on a calculated measure. 

This has got to be a simple solution that I am missing. 

Any help would be greatly appreciated! 

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[OwenAuger · Accepted solution]

Hello @Anonymous

A couple of options I can think of:

1. Use a visual level filter: If your visual includes Customer, add your existing YoY Measure as a visual level filter, filtered to "less than 0"
2. Handle the filtering in a measure: Create a new measure something like this:
   

   Sales Lost =
   SUMX (
       VALUES ( Sales[Customer] ),
       VAR CustomerYoY = [Existing YoY Measure]
       RETURN
           IF ( CustomerYoY < 0, CustomerYoY )
           // You could change the last argument to ABS ( CustomerYoY ) to view the result as a positive value
   )

Regards,

Owen

[EV]

This helped. However Im still not able to see the correct total. I tried to use this technique, but total shows only for the sales that has current year line.

SalesLost =
SUMX (
    VALUES ( Sales[Customer] ),SUMX(VALUES(Sales[Item Name]),
    VAR CustomerYoY = IF(OR(ISBLANK([Last Period Revenue]),[Last Period Revenue]<=0), [Prior Year Revenue],BLANK())
    RETURN
        IF ( CustomerYoY < 0, CustomerYoY )
))

Here is the concept.

All im tyring to do is show the lost customer sales, who didnt purchase for 6 months from selected month.

I created 3 measures, All works except the total. It seems to consider total onlly for current year or not at all.

Last period sales = 

CALCULATE( SUM(Sales[REVENUE_USD_FxAdj]),DATESINPERIOD('Calendar'[Date],LASTDATE('Calendar'[Date]),-7,MONTH)
)

prior year sales =

CALCULATE (
    -Sales[SALES REVENUE_PY_FxAdj],
    DATEADD ( 'Calendar'[Date], -1, YEAR )
)

lost customer sales = If las period sales is less than or equal to 0 or blank, return the prior year sales for that month in negative.

IF(OR(ISBLANK([Last Period Sales]),[Last Period Sales]<=0), [Prior Year Sales],BLANK()) 

I am seeing right values for the lost customer sales but total is blank.

I tried all possible SUMX, SUMMARIZE, still it doesnt work.

At the end I want to be able to see the total for each month in the below table

Please can you help, I am probably missing something @OwenAuger  Please can you help

[OwenAuger]

Hi @EV 

It looks like the issue is due to Customers/Items that are not present in the current filtered period being omitted from the SUMX/VALUES iteration.

It appears you don't have dimension tables for Customer/Item.

One solution based on your current data model would be to write SalesLost something like this.

The main change is to ensure that the Customer/items values iterated over are those that appeared in prior year, which are the only ones you need to consider for the SalesLost measure. These values are stored in the CustomerItemPriorYear variable.

SalesLost =
VAR CustomerItemPriorYear =
    CALCULATETABLE (
        SUMMARIZE ( Sales, Sales[Customer], Sales[Item Name] ),
        DATEADD ( 'Calendar'[Date], -1, YEAR )
    )
RETURN
    SUMX (
        CustomerItemPriorYear,
        VAR LastPeriodRevenue = [Last Period Revenue]
        VAR CustomerYoY =
            IF (
                OR ( ISBLANK ( LastPeriodRevenue ), LastPeriodRevenue <= 0 ),
                [Prior Year Revenue],
                BLANK ()
            )
        RETURN
            IF ( CustomerYoY < 0, CustomerYoY )
    )

Another solution would be create dimension tables for both Customer and Item and iterate over those, as they would naturally contain all Customer/Item values regardless of whether present in Sales.

Regards,

Owen

[EV]

Thank you very much for your time @OwenAuger 

I do have customer and Item dimension, When i used the below DAX, the report keeps failing with memory error, but works in small dataset.

SUMX(SUMMARIZE('Parent','Parent'[PARENT_NAME]),SUMX(SUMMARIZE('Item','Item'[ITEM_DESC]),IF([Last Period Revenue]<=0 || [Last Period Revenue] =BLANK(),[Prior Year Revenue],0)))

the DAX you have given here worked for me, however there are some values missing out, making the total incorrect, for each month.

I am sharing the PBI file with sample data in message, please could you check?

thank you very much in advance.

[Anonymous]

Thank you Owen! This got me on the right track. I appreciate the help!