Forum Discussion
Averagex Challenge
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:
3 Replies
- 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. 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.
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'[$])
