cancel
Showing results for
Did you mean:
Helper IV

## Sum based on 3 columns

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support

Hi @Cloud11 ,

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.

6 REPLIES 6
Super User IV

@Cloud11 ,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.

Proud to be a Super User!

Helper IV

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.

Super User IV

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))

Proud to be a Super User!

Helper IV

Hi Amit

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

Community Support

Hi @Cloud11 ,

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.

Helper IV

Thanks for excellent feedback Lionel

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform October Community Highlights

Check out the top community contributors across all of the communities

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors