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 need to create a column with weekly totals
I have different locations and different items selled on different days.
I need a column like below
Or else I need a column with weekly average values.
Like the first and second row should display an average of 7.5 and all other rows to display 15 as weekly average.
Can some one please help me with this
Solved! Go to Solution.
Weeekly Amount = DIVIDE ( CALCULATE ( SUM ( 'Table'[Amount] ), ALLEXCEPT ( 'Table', 'Table'[Week], 'Table'[Location], 'Table'[Item] ) ), CALCULATE ( COUNTA ( 'Table'[Item] ), ALLEXCEPT ( 'Table', 'Table'[Week], 'Table'[Location], 'Table'[Item] ) ), 0 )
I'm so sorry @Sean, I was little confused while asking the question.
It has to display based on the location, item and week - like below
Location | Item | Date | Week | Amount | WeeklyAmount |
A | X | 1/1/2017 | 1 | 5 | 5 |
B | Z | 1/1/2017 | 1 | 10 | 10 |
A | X | 1/2/2017 | 2 | 5 | 5 |
A | Z | 1/2/2017 | 2 | 10 | 15 |
B | Y | 1/2/2017 | 2 | 20 | 20 |
A | Z | 1/2/2017 | 2 | 20 | 15 |
As 4 and 6 rows has same location, item and week, then it has to show the total
Weeekly Amount = DIVIDE ( CALCULATE ( SUM ( 'Table'[Amount] ), ALLEXCEPT ( 'Table', 'Table'[Week], 'Table'[Location], 'Table'[Item] ) ), CALCULATE ( COUNTA ( 'Table'[Item] ), ALLEXCEPT ( 'Table', 'Table'[Week], 'Table'[Location], 'Table'[Item] ) ), 0 )
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 |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |