Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have been trying to solve this challenge for a couple days now. Your help is appreciated.
I am charting performance against an index using a Line Chart. The index data was straight forward to plot.
The sales/weight performance data was also straightforward, however it has a lot of noise and so a monthly average is desired in addition.
I have not been able to create the monthly average line correctly. Here is a sample of what the final chart should look like (the numbers are not in line with the data in the data table below).
A sample data table is below.
1) The data where the x-axis/line dimension is weeks (called Pricing Week in the data table).
2) There are three measures for this chart.
a) National Index (blue line)
SUM(IF([Value Type]= 'Index' , [Sales]))
b) Company Sales Price per ton (red line)
SUM(IF([Value Type]= 'CompanySales' , [Sales]))
/
SUM(IF([Value Type]= 'CompanySales' , [Weight]/[Unit Converter]))
c) Monthly Avg - Company Sales Price per Ton (green line)
I've tried many different formula approaches. Please help here
The line should find the total Sales for a month (based on Month[Pricing Week]) and divide that by the total Weight/Unit Converter for a month.
| Pricing Week | ID | Sales | Weight | Unit | Unit Converter | Pricing Month | Pricing Year | Value Type | Wood Type |
| 6/2/2023 | 1 | 300 | 6 | TONS | 1 | 6 | 2023 | CompanySales | Maple |
| 6/9/2023 | 2 | 637 | 12 | TONS | 1 | 6 | 2023 | CompanySales | Oak |
| 7/7/2023 | 3 | 600 | 10 | TONS | 1 | 7 | 2023 | CompanySales | Cherry |
| 7/14/2023 | 4 | 355 | 6 | TONS | 1 | 7 | 2023 | CompanySales | Maple |
| 7/21/2023 | 5 | 325 | 8 | TONS | 1 | 7 | 2023 | CompanySales | Maple |
| 8/4/2023 | 6 | 220 | 4 | TONS | 1 | 8 | 2023 | CompanySales | Maple |
| 5/31/2024 | 7 | 300 | 11700 | POUNDS | 2000 | 5 | 2024 | CompanySales | Cherry |
| 6/7/2024 | 8 | 356 | 5 | TONS | 1 | 6 | 2024 | CompanySales | Birch |
| 6/7/2024 | 9 | 309 | 5 | TONS | 1 | 6 | 2024 | CompanySales | Cherry |
| 6/14/2024 | 10 | 380 | 15100 | POUNDS | 2000 | 6 | 2024 | CompanySales | Maple |
| 6/14/2024 | 11 | 290 | 6 | TONS | 1 | 6 | 2024 | CompanySales | Oak |
| 6/21/2024 | 12 | 311 | 5 | TONS | 1 | 6 | 2024 | CompanySales | Oak |
| 6/28/2024 | 13 | 287 | 5.15 | TONS | 1 | 6 | 2024 | CompanySales | Chery |
| 6/28/2024 | 14 | 302 | 10500 | POUNDS | 2000 | 6 | 2024 | CompanySales | Oak |
| 6/28/2024 | 15 | 250 | 5 | TONS | 1 | 6 | 2024 | CompanySales | Maple |
| 7/5/2024 | 16 | 240 | 5 | TONS | 1 | 7 | 2024 | CompanySales | Maple |
| 7/5/2024 | 17 | 310 | 6 | TONS | 1 | 7 | 2024 | CompanySales | Maple |
| 7/5/2024 | 18 | 300 | 12000 | POUNDS | 2000 | 7 | 2024 | CompanySales | Maple |
| 7/12/2024 | 19 | 295 | 13000 | POUNDS | 2000 | 7 | 2024 | CompanySales | Birch |
| 7/12/2024 | 20 | 306 | 6 | TONS | 1 | 7 | 2024 | CompanySales | Maple |
| 7/19/2024 | 21 | 219 | 4 | TONS | 1 | 7 | 2024 | CompanySales | Oak |
| 7/26/2024 | 22 | 500 | 8 | TONS | 1 | 7 | 2024 | CompanySales | Oak |
| 8/2/2024 | 23 | 350 | 15000 | POUNDS | 2000 | 8 | 2024 | CompanySales | Maple |
| 8/2/2024 | 24 | 308 | 7 | TONS | 1 | 8 | 2024 | CompanySales | Cherry |
| 5/31/2024 | 25 | 3.93 | 5 | 2024 | Index | ||||
| 6/7/2024 | 26 | 4.22 | 6 | 2024 | Index | ||||
| 6/14/2024 | 27 | 4.67 | 6 | 2024 | Index | ||||
| 6/21/2024 | 28 | 5.11 | 6 | 2024 | Index | ||||
| 6/28/2024 | 29 | 4.82 | 6 | 2024 | Index | ||||
| 7/5/2024 | 30 | 4.51 | 7 | 2024 | Index | ||||
| 7/12/2024 | 31 | 4.42 | 7 | 2024 | Index | ||||
| 7/19/2024 | 32 | 4.18 | 7 | 2024 | Index | ||||
| 7/26/2024 | 33 | 3.87 | 7 | 2024 | Index | ||||
| 8/2/2024 | 34 | 4.05 | 8 | 2024 | Index |
Accepted Solutions
Take a look at the aggr() function to solve this.
In the screenshot below I 've loaded your data and applied the following two measures to the line chart.
Company Sales Price per ton
SUM(IF([Value Type]= 'CompanySales' , [Sales]))
/
SUM(IF([Value Type]= 'CompanySales' , [Weight]/[Unit Converter]))
Pricing Month avg Company Sales Price per ton
aggr(NODISTINCT
SUM(IF([Value Type]= 'CompanySales' , [Sales]))
/
SUM(IF([Value Type]= 'CompanySales' , [Weight]/[Unit Converter]))
, [Pricing Year],[Pricing Month])
I hope this will help you getting the visualisation that you need.
Take a look at the aggr() function to solve this.
In the screenshot below I 've loaded your data and applied the following two measures to the line chart.
Company Sales Price per ton
SUM(IF([Value Type]= 'CompanySales' , [Sales]))
/
SUM(IF([Value Type]= 'CompanySales' , [Weight]/[Unit Converter]))
Pricing Month avg Company Sales Price per ton
aggr(NODISTINCT
SUM(IF([Value Type]= 'CompanySales' , [Sales]))
/
SUM(IF([Value Type]= 'CompanySales' , [Weight]/[Unit Converter]))
, [Pricing Year],[Pricing Month])
I hope this will help you getting the visualisation that you need.
