Calculate YOY variance of distributions

[TP129]

Hi everyone,

I was hoping someone could help me out here. I wanted to calculate the distributions as follows:

    (1) percent of column total this year

    (2) percent of column total last year

    (3) YOY variance = (1) – (2)

Here are my formulas:

For (1): DIVIDE(SUM(Table1[Total]), CALCULATE(SUM(Table1[Total]), ALLSELECTED(‘Table2’[Level], ‘Table2’[Index]))) ---- I want to sort Level by Index

For (2): CALCULATE(DIVIDE(SUM(Table1[Total]), CALCULATE(SUM(Table1[Total]), ALLSELECTED(‘Table2’[Level], ‘Table2’[Index]))), FILTER(‘Date’, ‘Date’[Year] = MAX(‘Date’[Year]) – 1)) ---- basically just calculate (1), filtered for last year

I’ve attached a screenshot here and you can see I have a slicer next to my matrix. If I remove filter for 2021, my (2) formula works fine and I get the results for 2020. But if I choose 2021, my (2) still returns results for 2021. I’m not allowed to remove filter for 2021, it has to be there since I will have other tables on my report. How can I get my 2020 results then?

Another screenshot is the expected results for 2020 (you can ignore the number of months in each year)

My ultimate goal is to get the YOY variance (3). However, it didn’t work when I used the built-in formula for percent of column total. If someone could also help me with the built-in formula that would be great. If it's not possible to use it, I will continue to work around using DAX as above.

I really appreciate all your help and let me know if you need any further info.

Best,

[amitchandak · Accepted solution]

@TP129 , Try like

CALCULATE( DIVIDE(SUM(Table1[Total]), CALCULATE(SUM(Table1[Total]), ALLSELECTED(‘Table2’[Level], ‘Table2’[Index]))) , FILTER(all('Date'), 'Date'[Year] = MAX('Date'[Year])) )

For (2):

CALCULATE(DIVIDE(SUM(Table1[Total]), CALCULATE(SUM(Table1[Total]), ALLSELECTED(‘Table2’[Level], ‘Table2’[Index]))), FILTER(all('Date'), 'Date'[Year] = MAX('Date'[Year]) – 1))

[TP129]

Thank you so much! It works now.