The totals of YOY% in matrix showing wrong values if the case where last year sales is 0

[KON6777]

I have Sales data where I have measures to show Current year sales and Last Year sales and YOY%.

Formula I used for YOY% change is 
YOY = 
IF( Last_year_sale <>0, DIVIDE((Current_year_sale - Last_year_sale), Last_year_sale), "NA")

I tried using a 0 in second argument of DIVIDE as 0 as well without IF condition. But both the cases are giving me the same error.

Location hierarchy is Division - District - Store
The matrix is showing correct values in Store level. But the aggregations on District and Division level are showing wrong values as when summed up on that level of hierarchy the Last_year_sale is sum of 0s and values. So the condition of Last_year_sale <>0 is not working here.

Example.

District       Store         CY_sale       LY_sale         YOY%
    1                 1                  2000         0               NA
    1                 2                  3000        2000          50%
    2                 3                  2000         1500          33%
    2                 4                  3000         0               NA
    3                 5                  3000          2000         50%

Now YOY is working good at Store level as Last_year_sale <>0 can be determined. Now if we consider District level, for example district 1, I want YOY% to be avergae of 50%/2 which has to be 25%. But in my matrix it is showing 150%. In this example the % is small, but in my actual data where we have many new strores, the % change is going upto 200% when none of the stores have more than 5% YOY% when drilled down.

This is my first time posting a question here and beginner in PBI. But I hope I conveyed the question correctly. Can someone please help merectify this issue.

Other things I have tried,

I tried to replace 0s with nulls in original data set for LY sale column. Still the error persists. 
I also tried to replace "NA" with 0, null, BLANK(), "-" etc. Still the issue persists. 

[KON6777]

Thank you so much for the response. The 20% was a typo, which I corrected later.

I have tried it the DAX and for some reason it is gave me all zeros which I am assuming because I have four levels of Hierarchy (Phase- Division- District and then Store). I changed the YOY% like you have given here and I changed the avergae YOY% slightly as given below and I think have the corrected number. Thank you once again.

Final avergae YOY DAX that I used.
Bakery_sales_average_YOY% =

VAR TBL =

SUMMARIZE(

    'Sales Table',

    'Sales Table'[Phase],

    'Sales Table'[Division],

    'Sales Table'[District],

    'Sales Table'[Store],

    "@yoy", [YOY%],

    "rows",
CALCULATE(
DISTINCTCOUNT('Sales Table'[Store]),
ALLEXCEPT('Sales Table', 'Sales Table'[Phase],

    'Sales Table'[Division],

    'Sales Table'[District])

  )

)

VAR avg_yoy = AVERAGEX(TBL, DIVIDE([@yoy], [rows]))

RETURN avg_yoy

[danextian · Accepted solution]

Hi @KON6777 
Your YOY% result is confusing. I know 20% is supposed to be divided by 2 but how did you come up with 20% in row 2?  3000-2000 = 1000/2000 = 50%

That aside, create the following measures

YOY% = 
VAR _CY =
    SUM ( 'Table'[CY_Sale] )
VAR _LY =
    SUM ( 'Table'[LY_Sale] )
VAR _diff = _CY - _LY
RETURN
    DIVIDE ( _diff, _LY ) + 0
//+0 is necessary as you cannot format a blank value

Average YOY% = 
AVERAGEX (
    SUMMARIZECOLUMNS (
        'Table'[District],
        'Table'[Store],
        "@yoy", [YOY%],
        "@row count", CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[District] ) )
    ),
    DIVIDE ( [@yoy], [@row count] )
)

And then apply a dynamic format string to Avg YOY to show N/A

Please see the attached pbix.