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.
I have the following table:
ID | Date Description | Date Month | Date Year | Sales |
1 | Dec-2020 | 12 | 2020 | 100 |
2 | Jan-2021 | 1 | 2021 | 100 |
3 | Jan-2021 | 1 | 2021 | 50 |
4 | Feb-2021 | 2 | 2021 | 100 |
5 | Mar-2021 | 3 | 2021 | 25 |
6 | Mar-2021 | 3 | 2021 | 75 |
7 | Apr-2021 | 4 | 2021 | 100 |
8 | Apr-2021 | 4 | 2021 | 100 |
I would like to get the following table:
Date Description | Monthly Total | Overall Year Total | Average Year Total |
Jan-2021 | 150 | 550 | 137.5 |
Feb-2021 | 100 | 550 | 137.5 |
Mar-2021 | 100 | 550 | 137.5 |
Apr-2021 | 200 | 550 | 137.5 |
I was already able to get the Monthly Total just by summing Sales column. How do I create a measure / calculated column to compute for the Overall Year Total and Average Year Total?
Solved! Go to Solution.
Hi @lancea
You can create measures to return Overall Year Total and Average Year Total .
Monthly total = CALCULATE(SUM('Table'[Sales]),ALLEXCEPT('Table','Table'[Date Description]))
Overall Year Total = CALCULATE(SUM('Table'[Sales]),ALLEXCEPT('Table','Table'[Date Year]))
Average Year Total = [Overall Year Total]/CALCULATE(DISTINCTCOUNT('Table'[Date Description]),ALLEXCEPT('Table','Table'[Date Year]))
Then put the measures with fields ‘Date Description’ and ‘Date Year’ in a new table visual ,like this
In ‘Date Year’ filter , filter out data for 2020 .
I have attached my pbix file , you can refer to it .
Best Regards
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @lancea
You can create measures to return Overall Year Total and Average Year Total .
Monthly total = CALCULATE(SUM('Table'[Sales]),ALLEXCEPT('Table','Table'[Date Description]))
Overall Year Total = CALCULATE(SUM('Table'[Sales]),ALLEXCEPT('Table','Table'[Date Year]))
Average Year Total = [Overall Year Total]/CALCULATE(DISTINCTCOUNT('Table'[Date Description]),ALLEXCEPT('Table','Table'[Date Year]))
Then put the measures with fields ‘Date Description’ and ‘Date Year’ in a new table visual ,like this
In ‘Date Year’ filter , filter out data for 2020 .
I have attached my pbix file , you can refer to it .
Best Regards
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@lancea , Try measures like, with help from date table
This year Today =
var _min = today()
return
CALCULATE(sum('Table'[Sales]), FILTER('Date','Date'[Date] = year(today()) ) )
overall This year Today =
var _min = today()
return
CALCULATE(sum('Table'[Sales]), FILTER(ALL('Date'),'Date'[Date] = year(today()) ) )
overall avg This year Today =
var _min = today()
return
CALCULATE(Avergae('Table'[Sales]), FILTER(ALL('Date'),'Date'[Date] = year(today()) ) )
Thank you for this! The Overall Year Total works! However, I am getting incorrect result from the average as seen in the screenshot below:
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 |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |