Averagex Challenge

[JillHenninger]

Hi there. I'm building out a series of line graphs for my report. In my data, I have dollars, weeks, and departments for MY accounts, and for all other accounts. I want an average share line over the past year as a comparison. I started by trying the Average Line but - it only averages the visible weeks instead of the full year. (I'm only showing the latest 12 weeks or so, but I want the average line to be the full year)

I just cannot figure out how to do it. My "share" is a calculation (my dollars / (my dollars + all other dollars).

In the end, I would like two versions. One that does NOT change with the department changes. And one that does filter with the departments. Here's the goal:

[Ashish_Mathur]

Hi,

Share data in a simple table format (that can be pasted in an MS Excel file) and the reslt also in a simple tabel format.  Once the numbers turn out to be correct, we cal always build any visual we want.

[JillHenninger]

Ok! I've attached my workbook (hopefully correctly. 

Typically, I would use a series of VLookups to simplify dates and accounts, and I'd pull my data in the same order to line up Account vs All Other. Here, I've have 4 tables total, mapped. I've included that in the Excel file. I appreciate any help - I tried the formulas above and -it's just not clicking in my brain. The one that did work gave me the same line - it was averaging each data point instead of the entirety. Also, I only did 6 weeks here, but the data is reality is 52 single weeks (it will always been 52, old weeks will drop off, new ones added).

Thank you!

Table 1
Food Retailers	DEPARTMENT	Weeks	$
Division 1 Long	Dept 1	Week 1 Long	26,928
Division 1 Long	Dept 1	Week 2 Long	30,419
Division 1 Long	Dept 1	Week 3 Long	19,810
Division 1 Long	Dept 2	Week 4 Long	35,763
Division 1 Long	Dept 2	Week 5 Long	24,204
Division 1 Long	Dept 2	Week 6 Long	23,342
Division 2 Long	Dept 1	Week 1 Long	13,281
Division 2 Long	Dept 1	Week 2 Long	23,289
Division 2 Long	Dept 1	Week 3 Long	27,000
Division 2 Long	Dept 2	Week 4 Long	27,559
Division 2 Long	Dept 2	Week 5 Long	28,188
Division 2 Long	Dept 2	Week 6 Long	14,919

Table 2
Food Retailers	DEPARTMENT	Weeks	$
All Other 1 Long	Dept 1	Week 1 Long	213,483
All Other 1 Long	Dept 1	Week 1 Long	164,777
All Other 2 Long	Dept 1	Week 1 Long	284,638
All Other 2 Long	Dept 1	Week 1 Long	233,558
All Other 2 Long	Dept 2	Week 1 Long	281,022
All Other 1 Long	Dept 1	Week 2 Long	218,630
All Other 1 Long	Dept 2	Week 2 Long	250,625
All Other 2 Long	Dept 1	Week 2 Long	245,326
All Other 2 Long	Dept 2	Week 2 Long	240,457
All Other 1 Long	Dept 2	Week 3 Long	192,900
All Other 1 Long	Dept 2	Week 3 Long	284,263
All Other 2 Long	Dept 2	Week 3 Long	277,272

Account Reference
Division Long	All Other Long	Division Short
Division 1 Long	All Other 1 Long	Division 1 Short
Division 2 Long	All Other 2 Long	Division 2 Short

Time Reference
Time Long	Week
Week 1 Long	Week 1
Week 2 Long	Week 2
Week 3 Long	Week 3
Week 4 Long	Week 4
Week 5 Long	Week 5
Week 6 Long	Week 6

Calculations
Market Share	Total Avg	Avg by Dept
11.2	10.8	11.4
15.6	10.8	11.4
6.5	10.8	11.4
13.3	10.8	10.3
7.9	10.8	10.3
9.6	10.8	10.3
5.0	7.9	9.6
8.7	7.9	9.6
10.1	7.9	9.6
12.5	7.9	8.9
9.0	7.9	8.9
5.1	7.9	8.9

Example of Total Avg: 

Example of Avg by Dept:

Here, Division & Dept are hard-coded into the formula. Typically in Excel, I'd be using a PivotTable with vlookups for the Division and Time names; the data would be pulled in order so that Divisions & All other would line up. In Power BI, it's mapped

Existing Dax formulas:

Market Share $ = DIVIDE(SUM('MarketShare_CTA'[$]), [Total Market $])

Total Market $ = SUM('MarketShare_CTA'[$]) + SUM('MarketShare_xAOC'[$])

[Greg_Deckler]

Avg of All Dept, Full Year 1 = AVERAGEX(ALL('Table'),[Value])

Avg of All Dept, Full Year 2 = AVERAGEX(FILTER(ALL('Table'),[Year]=YEAR(TODAY())),[Value])

Avg of Filtered Dept, Full Year 1 = AVERAGEX(ALLEXCEPT('Table'[Department]),[Value])

Avg of Filtered Dept, Full Year 2 = AVERAGEX(FILTER(ALL('Table'),[Year]=YEAR(TODAY()) && [Department] = MAX([Department])),[Value])

Going to be something along those lines.