DAX Formulas for PowerPivot

Rob Collie

Mentioned 2

Offers information on the patterns and techniques of the formula language DAX.

More on Amazon.com

Mentioned in questions and answers.

I am pretty new on creating POWERPIVOT tables. I have searched for a bit of time now to resolve this problem but I have been unsuccessful so far. Here is my problem. As you can see below, I have created a POWERPIVOT table in Excel 2013 that is composed of two FACT tables, which are based on: 1) a sheet where the clients can insert initial budget entries; and, 2) another sheet where the clients can insert the post-initial budget entries. Also, a DIMENSION table has been added to the combination in order to add the following relationships:

Based on these relationships, I have clicked on Insert a PivotTable to create the following POWERPIVOT table that will be used to display the Initial Budget and Adjustments entries for analysis purposes. However, this table does not give me the total of both columns. I have thought that a calculated field would make it happen but this is where I am stucked as nothing let me sum the two columns like I was used to do with regular pivot tables in Excel 2007. The calculation of the two columns would logically equal the Current Budget as shown attached.

Thank you for your help on this.

With Power Pivot you don't use the calculated field feature of pivot table. You have to put a measure in your data Modell, which you can then add in the value part of the PT. Relationships alone are not enough.

currentBudget := CALCULATE (
    SUM ( fInitialBudget[Initial Budget] ) + SUM ( fAdjustement[Adjustements] )
)

if you are new with Power Pivot, I warmly recommend the book of Rob Collie "DAX Formulas for Power Pivot". I got a very good introduction of the PP capability with it. The 2nd Edition is on the way.

I have a query to pull clickthrough for a funnel, where if a user hit a page it records as "1", else NULL --

SELECT datestamp
    ,COUNT(visits) as Visits
    ,count([QE001]) as firstcount 
    ,count([QE002]) as secondcount
    ,count([QE004]) as thirdcount
    ,count([QE006]) as finalcount
    ,user_type
    ,user_loc
FROM
    dbname.dbo.loggingtable
GROUP BY user_type, user_loc

I want to have a column for each ratio, e.g. firstcount/Visits, secondcount/firstcount, etc. as well as a total (finalcount/Visits).

I know this can be done

  • in an Excel PivotTable by adding a "calculated field"
  • in SQL by grouping
  • in PowerPivot by adding a CalculatedColumn, e.g.

    =IFERROR(QueryName[finalcount]/QueryName[Visits],0)
    
  • BUT I need give the report consumer the option of slicing by just user_type or just user_loc, etc, and excel will tend to ADD the proportions, which won't work b/c

    SUM(A/B) != SUM(A)/SUM(B)
    

Is there a way in DAX/MDX/PowerPivot to add a calculated column/measure, so that it will be calculated as SUM(finalcount)/SUM(Visits), for any user-defined subset of the data (daterange, user type, location, etc.)?

Yes, via calculated measures. calculated columns are for creating values that you want to see on rows/columns/report header...calculated measures are for creating values that you want to see in the values section of a pivot table and can slice/dice by the columns in the model.

The easiest way would be to create 3 calculated "measures" in the calculation area of the powerpivot sheet.

TotalVisits:=SUM(QueryName[visits])
TotalFinalCount:=SUM(QueryName[finalcount])
TotalFinalCount2VisitsRatio:=[TotalFinalCount]/[TotalVisits]

You can then slice the calculated measure [TotalFinalCount2VisitsRatio] by user_type or just user_loc (or whatever) and the value will be calculated correctly. The difference here is that you are explicitly telling the xVelocity engine to SUM-then-DIVIDE. If you create the calculated column, then the engine thinks you want to DIVIDE-then-SUM.

Also, you don't have to break down the measure into 3 separate measures...it's just good practice. If you're interested in learning more, I'd recommend this book...the author is the PowerPivot/DAX guru and the book is very straightforward.