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
I have a simple salesamount by period and need to calculate a seasonality (% of yearly total). A sample of Sales:
Period | Amount |
2015-12-01 | 275 |
2016-01-01 | 20 |
2016-02-01 | 30 |
2016-03-01 | 32 |
2016-04-01 | 21 |
2016-05-01 | 17 |
2016-06-01 | 54 |
2016-07-01 | 23 |
2016-08-01 | 34 |
2016-09-01 | 21 |
2016-10-01 | 18 |
2016-11-01 | 19 |
2016-12-01 | 21 |
2017-01-01 | 32 |
2017-02-01 | 12 |
I have a relation between Sales and Dim Time. I need to get the % of year like this:
Period | Amount | % of year |
2015-12-01 | 275 | 100,00% |
2016-01-01 | 20 | 6,45% |
2016-02-01 | 30 | 9,68% |
2016-03-01 | 32 | 10,32% |
2016-04-01 | 21 | 6,77% |
2016-05-01 | 17 | 5,48% |
2016-06-01 | 54 | 17,42% |
2016-07-01 | 23 | 7,42% |
2016-08-01 | 34 | 10,97% |
2016-09-01 | 21 | 6,77% |
2016-10-01 | 18 | 5,81% |
2016-11-01 | 19 | 6,13% |
2016-12-01 | 21 | 6,77% |
2017-01-01 | 32 | 10,70% |
Any guidance ...
Solved! Go to Solution.
Hi @smulan,
First, add a year column using the formula: Year = YEAR(Test[Period]).
Then create a measure which calculates the total sum of each year, create calculated column which displays the percent of each year. You will get the result as the screenshot below.
Sum of each year = CALCULATE(SUM(Test[Amount]),ALLEXCEPT(Test,Test[Year]))
% of year = Test[Amount]/Test[Sum of each year]
Best Regards,
Angelia
Hi @smulan,
First, add a year column using the formula: Year = YEAR(Test[Period]).
Then create a measure which calculates the total sum of each year, create calculated column which displays the percent of each year. You will get the result as the screenshot below.
Sum of each year = CALCULATE(SUM(Test[Amount]),ALLEXCEPT(Test,Test[Year]))
% of year = Test[Amount]/Test[Sum of each year]
Best Regards,
Angelia
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |