Sum based on 3 columns

[Anonymous]

Hi Experts

I am trying to calculate the sum based on 4 other columns. Co. Period product and year..for each item and divide the sum of 2020 by 2021

So

Co.     Product     period         Year        value

A        apples        1                 2020         5

A        apples        2                 2020         5

A        apples        3                 2020         5

Co.     Product     period         Year        value

A        apples        1                 2021         7

A        apples        2                 2021         8

A        apples        3                 2021         9

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

Please refer to my .pbix file.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks for excellent feedback Lionel 

[amitchandak]

@Anonymous ,The information you have provided is not making the problem clear to me. Can you please explain with an example.
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Appreciate your Kudos.

[Anonymous]

Hi Amit

I want to divide 2020 values by 2021 values so for period 1 for apples it should be 5/7 and so on for each period. 

[amitchandak]

@Anonymous ,

Create year period column

year period = [year]* 100 + [period]

With period, year and year period create a new table, select distinct of all these three and join back with main table using year period

Have Rank in that table

new Column in the period table

Period Rank = RANKX(all('Date'),'Date'[year period],,ASC,Dense)

measures
This Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank])))
Last Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank])-1))

Last year same Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[year period]=max('Date'[year period])-100))

[Anonymous]

Hi Amit 

Do you have a pbix.. detailing this which I could view