Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
Thanks for excellent feedback Lionel
@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.
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.
@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))
Hi Amit
Do you have a pbix.. detailing this which I could view
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |