locked
Required DAX Query for Power Pivot RRS feed

  • Question

  • My question is, i have a field named “sales”, wich contain the sales of my clients and i need to separate the field by year, so i can create a “calculated field” called variation
    Example
    Year | Sales
    2010 $15
    2010 $10
    2011 $5
    2012 $25
    2012 $10
    2013 $5
    2013 $5
    2013 $50

    What a want to do:
    Year | Sales 2010 | Sales 2011 | Sales 2012 | Sales 2013
    2010 $15 $0 $0 $0
    2010 $10 $0 $0 $0
    2011 $0 $5 $0 $0
    2012 $0 $0 $25 $0
    etc, etc.

    Is this possible to do?
    or maybe you can suggest a different way of doing it.

    Tuesday, February 2, 2016 7:29 AM

Answers

  • If you look at the example AdventureWorksDW databases (does not matter what version), you can get an idea of how to design the tables in Fact (sales) and Dimensions (date). Then, you can create a PowerPivot model that joins the Fact (Sales) with the Date dimension. 

    You can create an additional attribute in the Date dimension for text 'Sales ' + Cast( Year as varchar(4)).

    Then, in a PivotTable in Excel, select the Year for Rows, new column 'Sales'_Year for columns and the measure will be your sales.

    It looks like you have multiple sales for a year, so you would need to include the additional attribute or dimension in the PowerPivot model to separate the different sales for the year in the model.

    Thomas


    TheSmilingDBA Thomas LeBlanc MCITP 2008 DBA

    Tuesday, February 2, 2016 4:23 PM