The column total for Negative Y on Y% doesn't appear to calculate correctly/.

Do I need another measure to calculate the total ie total negative movement divided by total Revenue PY

How can you calculate a 'total' year on year? Year on Year can only be one year compared to another, not all years compared to... what period?

Your 'desired result' didn't show a total comparison. I'd need to see a working example of how you would calculate this figure (based on the data in the file I attached for you), to understand what you're trying to achieve and how you want it calculated when different contracts start and finish on different dates.

Based on the data in the file.  If we take year 2018 for example

The Total negative movement is -1,217

The Total revenue PY is 7,152

The negative Y on Y% I want to calculate is -1,217 divided by 7152 giving :  -17.02%

Thanks for the example. In future please try and be this explicit when asking questions as it will help get to the answer much quicker.

You can change your Y on Y% formula as follows:

IF(
    HASONEVALUE(Table1[Customer No.]), 
        DIVIDE([Movement],[Revenue PY]), 
    CALCULATE( [Negative Movement] / [Revenue PY], 
        ALLSELECTED(Table1[Customer No.])
   )
)

Thank you.   I’m not at my computer to check but won’t this will alter the customer %ages as well?

to have this calculation in addition to what we already have I just create a new measure based on the above - correct?

No, the 'value if true' calculates the customer percentages and the 'value if false' calculates the total percentages.

This measure doesn't appear to calculate correctly

Negative Movement:=SUMX(VALUES(Table1[Customer No.]),IF([Current Cust Movement]>0,0,[Current Cust Movement]))

It is only giving the negative value where there is a value in the Total Revenue rather than the Revenue PY?

It returns the same result as your desired result example for 2017/2018 YoY, so please provide an example based on the file provided where the result differs from what you would expect.

The Y on Y % age is correct in that it is taking the total of the negative movement column and dividing it by the total of the Revenue PY column.

The problem is that the negative movement column is not calculating correctly and is missing some figures.

What I am trying to calculate here is customer retention - eventually I'd like a waterfall chart - as shown in the file.   I hope to be able to get the numbers for this chart from column totals in the pivot table.

More explanation in the file.

Thanks for all your help so far

Hi Alison,

See file attached. In cells T22:Y29 is a new PivotTable containing your measures. For some of the measures it's complicated to have the values at both the customer level of detail and the Grand Total for all customers level. You'll notice that these measures:

• Lost Customers Value
• New Customers Value
• Increased Customers Value

Do not return the correct results at the Customer level, but since you don't need them at this level I didn't waste time on it.

Mynda

Thanks.   I do require them at customer level as well

Hi Alison,

Please see attached. There are separate measures for the 'Grand Totals' (measures ending in 'Total'), which will support your waterfall chart and another set for the Customer level detail (measures ending in 'values').

The reason I've created separate measures is because I couldn't get Power Pivot to consistently identify which were the grand total rows using the usual techniques; HASONEVALUE, HASONEFILTER or ISFILTERED. The cause of this is the unusual way in which you want to aggregate the data e.g. only negative values that relate to an existing customer vs only negative values that relate to a customer you no longer have etc. This extra level of filtering causes the measures to behave irregularly.

There may be a way to do it, but I couldn't figure it out, sorry.

Mynda

Hi Alison,

I spoke to Matt Allington about the issue with the Lost Customers calculation and he came up with this formula:

=
SUMX (
    FILTER (
        ADDCOLUMNS (
            CROSSJOIN ( VALUES ( Customers[Customer No.] ), VALUES ( tblCalendar[Year] ) ),
            "total Sales TY", [Total Revenue],
            "Total Sales LY", CALCULATE ( [Total Revenue], SAMEPERIODLASTYEAR ( tblCalendar[Date] ) )
        ),
        [total sales TY] = 0
            && [Total Sales LY] > 0
    ),
    - [Total Sales LY]
)

It requires an additional table for Customers. Matt said:

"This lost customer thing is very complex. Normally you are working on data that is in a fact table. In the case of lost customers you are looking for data that is not in the fact table for customers that were in the fact table for previous years. This implies that you have to create a table of all possible outcomes somehow and populate it with the data you have to find the  missing data. I would not attempt such a thing without a star schema."

See file attached. Matt also has a forum dedicated to Power Pivot here: https://powerpivotforum.com.au/

Mynda

Thank you.   I can only see it on my phone at the moment.

the comment “I wouldn’t attempt such a thing without a star schema”.  Does the measure he has written create one on the fly so to speak?

thanks again for your perseverance with this

No, there is now an additional table for the Customers to support the measure Matt wrote.