Forum Discussion

JillHenninger's avatar
JillHenninger
Helper I
6 years ago

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...
Need Help
Ashish_Mathur's avatar
Ashish_Mathur
Super User
6 years ago

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's avatar
    JillHenninger
    Helper I
    6 years ago

    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 RetailersDEPARTMENTWeeks$
    Division 1 LongDept 1Week 1 Long26,928
    Division 1 LongDept 1Week 2 Long30,419
    Division 1 LongDept 1Week 3 Long19,810
    Division 1 LongDept 2Week 4 Long35,763
    Division 1 LongDept 2Week 5 Long24,204
    Division 1 LongDept 2Week 6 Long23,342
    Division 2 LongDept 1Week 1 Long13,281
    Division 2 LongDept 1Week 2 Long23,289
    Division 2 LongDept 1Week 3 Long27,000
    Division 2 LongDept 2Week 4 Long27,559
    Division 2 LongDept 2Week 5 Long28,188
    Division 2 LongDept 2Week 6 Long14,919

     

     

    Table 2   
    Food RetailersDEPARTMENTWeeks$
    All Other 1 LongDept 1Week 1 Long213,483
    All Other 1 LongDept 1Week 1 Long164,777
    All Other 2 LongDept 1Week 1 Long284,638
    All Other 2 LongDept 1Week 1 Long233,558
    All Other 2 LongDept 2Week 1 Long281,022
    All Other 1 LongDept 1Week 2 Long218,630
    All Other 1 LongDept 2Week 2 Long250,625
    All Other 2 LongDept 1Week 2 Long245,326
    All Other 2 LongDept 2Week 2 Long240,457
    All Other 1 LongDept 2Week 3 Long192,900
    All Other 1 LongDept 2Week 3 Long284,263
    All Other 2 LongDept 2Week 3 Long277,272

     

    Account Reference 
    Division LongAll Other LongDivision Short
    Division 1 LongAll Other 1 LongDivision 1 Short
    Division 2 LongAll Other 2 Long

    Division 2 Short

     

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

    Week 6

     

     

    Calculations  
    Market ShareTotal AvgAvg by Dept
    11.210.811.4
    15.610.811.4
    6.510.811.4
    13.310.810.3
    7.910.810.3
    9.610.810.3
    5.07.99.6
    8.77.99.6
    10.17.99.6
    12.57.98.9
    9.07.98.9
    5.17.98.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'[$])