When you have general PowerPivot question you can ask them here. I’ll try and help you asap :).

I have started archiving old questions, everything before 2014 can be found here: http://www.powerpivotblog.nl/Askyourquestionsarchive.html

When you have general PowerPivot question you can ask them here. I’ll try and help you asap :).

I have started archiving old questions, everything before 2014 can be found here: http://www.powerpivotblog.nl/Askyourquestionsarchive.html

Hi Kasper,

No sales no budget. I’m using the below formula to calculate budgeted values, however when there are no sales no budget is calculated neither (as you can imagine from the DAX). How can I always get budget figures even with no sales?

=CALCULATE(

SUM( Act1213Bud[Act2012] );

FILTER( ALL( Act1213Bud[YearMonth]);

CONTAINS( VALUES( DatesTable); DatesTable[YearMonth]; Act1213Bud[YearMonth] ) );

FILTER( ALL( Act1213Bud[Country]);

CONTAINS( VALUES(Sales[Country]); Sales[Country]; Act1213Bud[Country]) ))

Thank you for your help!

BR,

Mikko

Hi Mikko,

I am not sure I understand what you are trying to do here? does Act1213Bud[Act2012] contain the budget value? And why cant you use the relationships between the tables? What does your schema look like?

Thanks,

Kasper

PS your formula as is can be simplified using:

=

CALCULATE (

SUM ( Act1213Bud[Act2012] );

ALLNOBLANKROW (DatesTable[YearMonth]);

ALLNOBLANKROW (Sales[Country]);

)

Dear Kasper,

Thank you for your answer! The schema is very simple with the date table, budget/actual2012 table which contain budget for 2013 and actual result from 2012 set for the first day of month and finally real sales from DB from Sales table. The tables are linked with the date table the usual way. I can compare real sales towards budget and actual but if some country is missing sales for the month then budget and actual values are also missing.

BR,

Mikko

PS the simplified formula produces cartesian product for 2012 actual result.

Hi Mikko do all these tables have relationships? Also would it be possible to send me a simplified workbook? Just using Excel tables but the same schema and that shows the issue. I’ll answer by doing a blog post on it..

Dear Kasper,

I have now made the simplified example. Can you kindly email me address to send it to you.

BR,

Mikko

Hi Mikko,

please use kadejoatmicrosoftdotcom

Kasper

Hello Kasper,

I’d like to get your opinion on whether the set up I have is most efficient or if there are any other recommendations.

I currently have 3 tables.:

1st table is named “stock” and has 1Million rows

2nd table is named “optimization” and has 400K rows

3rd table is named “fill rate” and has 100K rows

The “stock” table is downloaded via sql from our MRP system

The “optimization” table is also downloaded via sql from our MRP system

The “fill rate” table is downloaded from SAP via business warehouse.

Once all three are downloaded, I import all three via text files into Microsoft Access.

I then create a query to join the three tables and bring in the necessary columns and I also create calculated columns .I run the query and make it a new table in Access and I name the table ‘analysis’ in Access

After those steps I connect powerpivot to the table in Access named ‘anaylsis’ and begin to perform my analysis in Powerpivot.

Is this the most efficient method to use? If I were to use SQL Server Express instead of MS Access would it make this process easier and quicker to run? What are your thoughts?

Hi,

What is the reason that you use Access in this scenario?

I would try to see if you can import as much as possible directly into PowerPivot through the SQL connection or text files and keep the 3 tables as they are, create the relationship in PowerPivot and skip access.

You also might want to investigate Power Query as intermediate step to do some light weight ETL.

Hope that helps.

Hello Kasper, Thank you for your reply. I utlize Access to create the joins between the data as I am unable create this relationship without modifications in powerpivot alone. I also create calculations in access as well prior to uploading into powerpivot. I guess I could create another column in the power pivot sheet and concatenate two columns to form the join and relationship that way. I will also give power query a try as well. thanks

Hello Kaspter,

Im using a moving average formula and for some reason whenever i show the dates as a row or column i have a blank space with a balance.

The ending balance formula is the following:

CALCULATE(SUM(Accounts[CF_BALANCE_FINAL]),Accounts[CF_ACTIVE_FILTER]=1)

The moving avg formula is the following:

if(HASONEVALUE(Accounts[Snapshot_Date]),

calculate([MEASURE_ENDING_BALANCE]/COUNTROWS(VALUES(Accounts[Snapshot_Date])),DATESBETWEEN(Accounts[Snapshot_Date],FIRSTDATE(PARALLELPERIOD(Accounts[Snapshot_Date],-11,month)),lastdate(PARALLELPERIOD(Accounts[Snapshot_Date],0,month))),all(Accounts[Snapshot_Date])))

Any help would be appreciate.

Thanks.

Hi Kasper,

First all of thanks for your efforts in this blog! Do you know how to calculate discounts with DAX. Lets say you have a table (representing a discount schema for individual customer of group). For example you sale bicicles, you give a 10 dollar discounts on all bicycles, 25 dollars when is a trycicle, 45 dollars to italian bycles and for specifics model you give different rebates. You can see the structure (bicicles, bicycle type, country of origin, product number.

How to create a formula that recognizes these levels? how to deal with overlapping contracts, etc… really have no clue….

Any tips??

Txs!

Hi Kasper,

Can you give me a simple calculation for removing the phone number from a customer name field (e.g. Phil Brown (561) 398-1234)? I managed doing it by using three calculated columns but I’m sure there is an easier way to do it.

Hi Kasper,

I saw your blog post on Sparklines and how you have used Slicers on them. Is there a way you can apply the same slicer on a Chart as well as a Sparkline which would be based on the same PivotTable? Do you have a sample like that?

Hi Kasper,

I originally created a PowerPivot in Excel 2010. I eventually upgraded to Excel 2013 because I was attempting to pull millions of lines of data and Excel 2010 couldn’t handle the volume. However, my company uses SharePoint 2010 / SQL2008R2 so I’m unable to schedule a manual refresh or use the slivers. I (now!) know there are some incompatibility between Excel 2013 and our current versions of Sharepoint and SQL. Do you have any thoughts in regard to how to work around this? Again, because Excel 2010 can’t handle the volume we need it to, rebuilding the PowerPivot in 2010 isn’t really a solution (and that is the only recommendation from our Sharepoint team). Any suggestions are MUCH appreciated!

Best Regards,

Jess

Hi Kasper,

I am having a similar issue to Jess. My newly formed team has to deal with over 4 million lines of data. It seems like 64 bit version of Excel will be mandatory for us, correct?

My question is – if my team has 64 bit, will the readers of our reports ALSO need 64 bit or can they read our reports/analysis with 32 bit?

Thanks!

Hi Jess and Ellia, there is no difference between Excel 2010 and Excel 2013 on number of rows. I would recommend installing the 64 bits version of Excel, that will allow more data to be loaded in memory on both 2013 and 2010.

Its not necessary that you need to install the 64 bits version, it really depends on the data and if it can be compressed enough to fit in memory. There are some tricks see this help article:

http://office.microsoft.com/en-us/excel-help/create-a-memory-efficient-data-model-using-excel-2013-and-the-power-pivot-add-in-HA103981538.aspx

The readers of the report only need 64 bits if you want to share the report with them directly. If you use sharepoint they dont need Excel only a web browser.

Hope that helps

Kasper

Hi Kasper,

Thanks for your quick response and suggestions. It sounds like Excel 2010 64 bit is the way to go for us. Unfortunately, we had a local consultant come in and tell us we absolutely needed Excel 2013 to handle all the data which is why we upgraded – I should have come here first!

Thanks again,

Jess

Do you ever connect powerpivot to SQL express? If so do you have any problems establishing the connection? The server name doesn’t appear in the dropbox in powerpivot.

Thanks

Hi, I would expect this to work. What happens if you just type in the name of the database and continue?

Thanks,

Kasper

Hi Kasper,

I’m using powerpivot to review sales and website traffic information. I connect to my data source using a flat file (.txt). After setting up the pivot table, the sales information sums individual product sales. Each of the individual products have a 10-digit alphanumeric identifier.

I noticed when I sum using powerpivot that the total is different than the total when using a normal pivot table in excel. After performing an audit it appears that powerpivot is omitting rows of data when summing. Powerpivot recognizes the row for the product identifier, but the sales data numerical value is blank, thus leading to the discrepancy in the total amount.

Any ideas on what I’m doing wrong?

Thanks,

Sean

Hi Sean,

Are there relationships between the tables? Are you sure that the values on both side are the same?

Is the column that you use the aggregate from the same type? can you see the values that are omitted in the sales table?

Thanks,

Kasper

Hi,

I am new to Power Pivot. I am trying to create variance measure for Budget vs actual and YTD calculations. Is there simple formulas I can follow for both?

Thanks!

Hello

I am looking to create a DAX expression which provides a countdistinct on a specific variable where some other variable matches something else across a full table.

Can anyone help?!

Hi Matthew,

Maybe something like this:

Calcualte(distinctCount(table[col]), Table[color]= “white”)

Would that work?

Thanks,

Kasper

Thanks for your note. Want I envisaged doing was creating this count for each category without defining the category in advance. So let’s say the categories are:

France

Wales

Ireland

I wanted a way to get a count distinct for each category.

I have another question. I am looking to create a box chart and so I need min, max, quartile 1, quartile 3. Is there a way to calculate all of these across a table of data. Again, I want it calculated in way that I can make the section on a filter on a pivot.

Let’s say we are calculating salary data. I want to create the expression in the data structure and then make the selection in the pivot.

OK I now understand what I need to do here. Simply =distinctcount across the column and add as a measure NOT a calculated field.

My question on how how to create quartile 2 and 3 values as a measure is the tough one for me!

Hi Matthew,

Quartile is not easy to do in DAX, but check out this blog post on Rob Collies blog:

http://www.powerpivotpro.com/2011/09/creating-accurate-percentile-measures-in-dax-%E2%80%93-part-i/

Thanks,

Kasper

Hi Kasper –

All the references to working with time I have seen are actually working with dates.

I am analysing a number of daily processes. I have log data in PowerPivot which lists for each day, what time of day each process completed. I want to chart the time each peocess completed (Y-axis) against the date (X-Axis) but PowerPivot does not seem to be able to recognize the time as a value, to average the time (say if I summarise at a monthly level).

The only way I have found around this is to convert the time to the an integer containing number of minutes since midnight. This works bot the values on the Y-axis (time) are meaningless to most people.

Is there a way to work with time-of-day as a value?

Thanks,

Peter

I’m looking to find a way to easily create multiple horizontal bar charts.

Let say I have some categories running on the X axis. Let’s say they are IT, Sales, Marketing and I have a measure in the columns, number of jobs, for Q1 – Q4. I want to show a horizontal bar for each category for each quarter.

I could do this with conditional formatting but was hoping for something which looks better!

SUBJECT: Max number of measures allowed on measures grid.

Hi Kasper,

Do you know if there is a maximum number of measures that can be entered on a single measures grid?

I have an Excel 2013 workbook with about 300 measures, 130 of which are on the same measure grid. When I tried entering the 131st measure, I received the message “Sorry, Powerpivot can’t save these changes back to the Data Model.”

After a bunch of trial and error, I found that I could only enter my additional measures by putting them in a different measures grid so as to keep the maximum number of measures on any one grid under 130.

I couldn’t find any references to this issue on the web, so am wondering if this is normal, or if there might be something specific to my workbook going on.

Thanks,

Tak

Hi Tak,

I am not aware of a limitation like that but it sounds like a bug, can you file a bug at http://connect.microsoft.com/sqlserver that will allow us to take a look at the issue.

Thanks,

Kasper

Kasper,

Hope this finds you doing well. My question – I have data that I’m gathering by week and filtered at the year level. The rows are month, then week. The columns are current week, MTD and YTD measures. My issue is with the YTD data. I want it to stop accumulating with the last MTD, instead it repeats all the way down to the end of the year. Anyway to get the TotalYTD function to stop at the last totalMTD entry that is >0?

Thanks Kasper. Subsequent to my prior post regarding “Max number of measures allowed on measures grid”, I tried to replicate the issue on a fresh workbook and did not run into the same limitation, so it appears specific to the nuances of a specific workbook. I’m guessing the apparent workbook-specific nature of this issue makes it inappropriate to reporting at http://connect.microsoft.com/sqlserver but let me know if I should report it anyway and I will do so.

Regards,

Tak

Hi Kasper,

I haven’t set up any relationships between tables. The sales information is numeric and formatted as a Decimal Number.

The column for the aggregate total is the same type. I can see the values that are omitted in the sales table (please see attached examples.).

I’ve adjusted the data type and format in the powerpivot menu but the discrepancy remains the same. I have been able to correct the values when I change the import file from .txt to .csv. However, I’d rather avoid this extra manual step and I cannot publish my SQL queries in this format. I’m trying to automate the reporting as much as possible.

Thanks for your help!

-Sean

HI Sean, so this means that even when you do a simple aggregation for the entire sales table you get “wrong” results? Any chance you can send me the workbook?

Hi Kasper,

I have dataset with around 80 products and I am running top x products by revenue.

I d like to add x+1 line in the report showing all the other products which are ranked below top x, aggregate them into one line – “other products”.

e.g.

top 5 products table

1

2

3

4

5

other products total

grand total

Thank you for your help!

Petr

Hi Kasper,

I have to loop through the dataset having columns Batch, CustomerNo, Date ( have single date column) and calculate difference between dates within CustomerNo and Batch combination, then eliminate rows where difference is less than 10 days and keep doing that until all rows with difference less than 10 days are eliminated with in the group. It can be done in SQL using CTE but not sure if this can be achieved in powepivot? Any inputs are appreciated.

Hi, I see potentially two options:

1 use a calculated column to loop through the table

2 use PowerQuery and M to go through the table

Thanks,

Kasper

Hi Kasper,

We’re searching for a hoster of Sharepoint PowerPivot Services on the Web. Do you use/know or could you recommend such hosters?

Does Microsoft directly provide such services (for example through Office365 Sharepoint plans), too?

Thanks a lot for any hint.

Thom

Hi Thom, we microsoft actualy now has PowerBI where you can share PowerPivot workbooks. Check out http://www.powerBI.com

Thanks,

Kasper

Kasper,

I have a 2.2 million row ShippingData.csv file that I can successfully load into the DataModel via the [OtherSources | TextFile] option from the PowerPivot 2013 Ribbon.

As a trial, I tried loading that same file into a new 2013 DataModel using PowerQuery, but it failed giving me an “Out of Memory” error!

I even tried eliminating some fields but the import continues to fail.

Do you have any ideas what might be causing that to happen?

Thanks,

Chris

Is it possible to be able to use the slicer for inclusion criteria of AND instead of OR. So basically, if I have a people and list of states they have been to. When I do a slicer, I want, if I select Ohio and Maine, I want to display only the people that have been to BOTH Ohio AND Maine…

Hi Tracy, you could use a calculated field and disconnected slicers to achieve this. see more info on: http://www.powerpivotpro.com/2013/08/moving-averages-controlled-by-slicer/. I’ll probably show it to you in a blog post soon. Its an interesting case.

Thanks,

Kasper

Hi Kasper,

In my model, I have two memory-expensive SUMX functions (call them #A & #B). My 32-bit Powerpivot could not calculate the measure ‘[#A + #B]‘, however, it could calculate both measures (at the same time) when separated into two measures ‘[#A]‘ & ‘[#B]‘.

Does it always require less memory to split a measure into two or more of its components?

(…I previously assumed the opposite to be true)

Thank you

-Sean

I think its safe to say that splitting measures up doesn’t always result in lower memory usage. It really depends on the calculations. Can you specify your formula’s?

Thanks,

Kasper

Hi Kasper,

Thank you for your recent post on RANKX(). I have used the same setup (except deal with the blanks a bit differently) in my company’s calculations. I would like to use content from your blog post to teach others in my organization and just wanted to see if I could get your permission to copy some of the info from your post into an internal wiki. Please let me know if this would be ok.

Thank you,

Andrew

Hi Andrew,

Sure go ahead!

Thanks,

Kasper

I have 10 millions of jobs I have aggregated. Each job has a job title. This can be broken down into component words. I then want to run an automatic frequency analysis for each word across each month for 2013. I need a set of DAX expressions that allows me to both split the job title field into component parts and then produce a cross tab showing a count of each word across the full universe of jobs for each month.

Possible?

Hi Matthew,

Just to make sure I understand. You have a predefined table with words (not auto generated based on the jobs). Then you want to see how often those words are being used in the jobs table?

Thanks,

Kasper

Matthew,

Have you thought of doing this text-string/word frequency analysis using another tool? (Don’t get me wrong, I’m a huge fan of PowerPivot.)

I’ve seen this done in some example programs for both “RapidMiner” and in the “R” language tutorials. Try doing a web search on those.

Chris

Hi,

I’m new to Power Pivot tool,I have 2 tables information one is Master table and Another is Transaction table both the tables are related to CustomerID Column.In Transaction table some of ID’s are repeating but InvDate Column is Different.When I drag the Sales Amount column into report it is showing Sum of all CustomerID matched records,my requirement is need to pick max Invdate value from table where there is a same Customer ID,I written DAX formula for same but it is not Picking max InvDate.

DAX Formula:

=CALCULATE(SUMX(‘IR’,’IR’[SalesAmount]), FILTER(‘IR’,SEARCH(“*Manage*”,’IR’[Name],1,0)>0),Filter(‘IR’,’Sales’[Customer ID]=’IR’[CustomerID]&& ‘IR’[InvDate]=Max(‘IR’[InvDate])))

Another approach that I followed is create one calculated column for Max(‘IR’[InvDate])

that calculated I use in above query but there is no luck.

In Above Query IR and Sales are two tables.

Regards,

Praveen

Dear All,

I have a requirement in Power Pivot for excel 2013.I have one master table Employee and Transaction table sales, in My transaction table I have only employee Name I can not give relation between Employee Master and Sales table because of employee names are not unique in Employee Master.I written DAX query to get EmployeeID in Transaction table.

DAX query I shared in below:

=CALCULATE(VALUES(‘Employee’[Emp ID]),FILTER(‘Employee’,’Employee’[Name]=Sales[Ename] && ‘Employee’[End date] Emp(Eid),Emp(Eid)

I am working with a tabular model in SSAS 2012 that supports several fact tables. We’re trying to build perspectives into the model to support a couple different viewpoints. When we browse the model from Excel we see all of the facts grouped by the table they came from, but when we try to browse a perspective the facts from all of the different tables are lumped together. Browsing the perspective from Management Studio maintains the original table structure. Is this a known issue with Excel or just a feature we’ll have to live with?

Hi Kasper

GREAT resource.

Here’s an easy one, how can I determine the number of rows in a pivot table without using VBA? Is there an excel formula I can use that can do it simply?

My issue is I have a pivot that varies based on a category selected. E.g. category A might return 100 rows and 10 columns and category B might return 90 rows and 7 columns.

=rows() obviously doesn’t take a pivot table as a reference so it’s not dynamic.

Thanks in advance

Hi Nicholas, there is no good way to do this in PowerPivot. You could however do it in Power Query.

Nicholas,

If there is a column of your spreadsheet that is unique (no other formulas or values in the column) to your Pivot Table (Let’s use column B for example), then you could simply use the =COUNT(B:B) or COUNTA(B:B) to get the total number of rows.

You may want to make adjustments for headers and grand totals, such as =COUNT(B:B)-n .

Dealing with SubTotals is a whole other issue!

Thx Chris for helping out!

I have a measure “Count Accidents” =DISTINCTCOUNT([Longitude])and I now want to create a Countif measure to count values >=-1.08 and =-1.08,[Longitude]<-2.8), I cannot get it to work, help please as I have failed to find a Google solution.

Sorry for some reason the measure I pasted into my question did not appear in my message so the measurement I have tried is =CALCULATE([COUNT ACCIDENTS],[Longitude]1.08)

Hi Andy,

An expression like this should work: =CALCULATE([COUNT ACCIDENTS],Table1[Longitude] > 1.08).

What is the error message you are getting?

Kasper

Hi Kasper,

I am trying to find a way in DAX to creat somthing like NEXTNONBLANK. I need to calculate WON/LOST Value due to the price change, and for most of the customers first price, would be “START PACK” wchich is 50% cheaper, I can’t take simply next month sales because, there is no rule, with buying frequency over the year.Other thing that might resolved this issue would be a function that states when the price was changed, and I have no idea how to write it.

Try a Measure formula like this:

Filtered Accidents:=CALCULATE([Count Accidents],Filter(Table1,[Longitude]>=-1.08 && [Longitude] = -1.08 AND < -2.08 would never result a value being returned. Hoping this was either a typo, or you meant OR, not AND. In which case change the && to ||

Chris

Sorry, something went crazy in the previous post…

Try a Measure formula like this:

Filtered Accidents:=CALCULATE([Count Accidents],Filter(Table1,[Longitude]>=-1.08 && [Longitude] = -1.08))

The way you described your filter criteria (> -1.08 AND < -2.08) would never result a value being returned. Hoping this was either a typo, or that you meant OR, not AND. In which case change the && to || in the FILTER portion.

Hello Kasper,

I put company related measures on PowerView and I want to add top 5 companies from same industry where is company. Do you have some sugestions how to do that?

I need to calculate the % of total at aggregated level. I have a powerpivot model that have the following fields; SKU,brand,sales,cost,qty,Grossmargin by SKU.

I created a pivot table that summarizes the data by brand so it has Brand,sale,cost,qty , GrossMargin (use a dax formula for Gross margin). I need a dax formula that calculate the % of the total by brand.

Are you familiar with this error? “Errors in the high-level relational engine. The following exception occurred while the managed IDataReader interface was being used”

I need to calculate the average order size of all orders that contain at least one sku that is category device. If I filter by device, I can’t include the value of the other items. I am thinking i need to use a ALL calculation, but not sure how.

Hi Kasper,

A recent organization change at my company now has me working very closely with SAP (which I currently have no experience in). I have experience with PowerPivot but none with PowerQuery. What is the easiest way to get SAP data into PowerPivot? Or, which skills should I be learning or books should I be reading to hit the ground running with PowerPivot and SAP.

Thank you

-Sean

Hi Sean,

I would recommend going with power query and SAP http://www.microsoft.com/en-us/powerBI/SAP.aspx#fbid=wBOvyRYwQ-p

Hi Kasper. I never received a response for my question a few months back. Can you give me a simple calculation for removing the phone number from a customer name field (e.g. Phil Brown (561) 398-1234)? I managed doing it by using three calculated columns but I’m sure there is an easier way to do it.

hi Liz,

Rica’s example does the trick I would say.

Hi Liz, try this in calculated column “left([CustomerName],FIND(“(“,[CustomerName],1)-1)” but if you will have “(” in customer name it will remove everything after ( ,for ex: “Liz(Liz” – above formula will return “Liz” and remove “(Liz”

I was wondering how people apply links to BISM models on SP so when the user creates a PowerView report from a link the default save directory is not the data connection folder.

Hi Philip,

Do you mean when you create a PV report in Excel?

Thx,

kasper

Here’s the goal: Create a DAX measure to count the number of dates where the sum of category “C” transaction amounts per date is greater than $200.

Date Category Amount

5/6/2014 A 122.00

5/6/2014 C 87.00

5/8/2014 A 110.00

5/8/2014 C 50.00

5/8/2014 C 50.00

5/8/2014 C 120.00

5/9/2014 A 280.00

The DAX measure would return the value, 1, because only 5/8/2014 has a daily total exceeding $200 for category “C”.

Seems like I must be missing something obvious in creating the measure, but it has escaped me.

Any suggestions?

Ron,

Try these measures:

TotalAmount:=SUM([Amount])

CSales:= CALCULATE([TotalAmount], FILTER(Table1,[Category]=”C”))

and

nDates of C gt 200:=CALCULATE(Distinctcount([Date]),FILTER(ALL(Calendar),[C Sales]>200))

Chris Gilbert

cgilbert@jerviswebb.com

Ron,

Alternatively (and probably better for large transactions table), try:

AltMeasure CSales over 200:=Calculate(DISTINCTCOUNT(Calendar[Date]),FILTER(ALL(Calendar),[C Sales]>200))

Hi Kasper,

Here’s one for you that might be interesting and that I haven’t seen addressed elsewhere on the web. I’ve always worked around this issue by using VBA, but it would be nice to handle natively in Power Pivot instead.

Let’s say we have a dates table and fact table linked by a field called DatesID. The dates table has one field called DatesID and the fact table has two fields (DatesID and Quantity) as follows:

Date, Quantity

5/1/14, 1

5/2/14, 2

5/3/14, 3

We have a measure for CurrentQuantity:=SUM(fact[Quantity]). When we select 5/3/14 on a slicer for dates[DateID], we get 3 from Pivot1. Everything is fine at this point.

Where I run into trouble is with Pivot2 where I want to list all dates within the last 30 days of the slicer date along with the corresponding quantity.

So for example: By selecting 5/3/14 on the date slicer, we want to produce the following on Pivot2:

Date, Quantity

5/1/14, 1

5/2/14, 2

5/3/14, 3

Similarly, when we select 5/2/14 on the dates slicer, we would get 2 from our CurrentQuantity measure and 5/3/14 would drop from pivot2.

Is this possible without using VBA to filter the date range for pivot2?

Getting to a pure Power Pivot solution for this would be AWESOME!

Many thanks for all you do for the Power Pivot user community, whether or not you can help with this one

Regards,

Tak

Hi Tak,

this should be pretty straightforward. I’ll do a blog post over the weekend on how to solve it.

Thanks,

Kasper

I just got the book and am anxious to work through it. Where can I get the datasets that were used for the examples in the book?

That would be wonderful…thanks Kasper! Regards, Tak

Tak, check it out here: http://www.powerpivotblog.nl/show-the-sum-of-sales-for-the-last-3-days-based-on-date-selection/

Is there a way to get a max of a measure?

For instance, I am summing up a distinct count of stores selling for a certain time period. Then I need the max from those groups to use as a filter.

So for instance, Period 1 distinct stores is 80, Period 2 is 95, Period 3 is 60. I need to be able to use the max of each group.

So my end result would be a formula to sum sales for those periods where the distinct store count is somewhere near the max (like within 10 stores.) So if Period 10, for instance, only had 35 distinct stores, it’s sales dollars would not be part of the sum.

Can’t seem to get anywhere with it. Thanks, looking forward to the ebook version of Dboard & Reporting.

Hi Kasper,

I love Rob Collie’s book “DAX Formulas for PowerPivot: A Simple Guide to the Excel Revolution” and I have pre-ordered your book and am very much looking forward to receiving it.

When searching for PowerPivot books, I see two by Rob Collie with slightly different titles, the other being “DAX Formulas for PowerPivot: The Excel Pro’s Guide to Mastering DAX”. Are these totally different books or just alternate titles? And if they are different, where can I purchase the latter – Amazon is out of stock.

Thank you

-Sean

I think these are the same with a different title.

Hi Kasper,

I loaded data into my Power Pivot datamodel using Stored Procdures. At this point I need an additional column in my datamodel. I have added it to my stored procedure, but Power Pivot doesn’t seem to pick up on it when reload the model. When I add this as a new table than it does pick up on that. Is this expected behavior from Power Pivot?

Kind regards,

Stijn

Never mind. I already found the solution. A simple refresh was not enough. I had to go to table properties and press “save”.

Kind regards,

Is there a way to parameterise a power pivot such that the inputs for the query used to load the data model are taken from a worksheet?

I need to build a dashboard but only import the most relevant data each time.

Mathew,

You might find the following link useful:

http://dmoffat.wordpress.com/2013/08/14/building-a-flexible-and-efficient-client-side-powerpivot-solution-in-excel-2013-2/

Regards,

Tak

Dear Kasper, please help on a measure to solve this problem. I have this table:

Per No Amount Date

123 230 5/31/2014

123 300 4/30/2014

145 125 4/30/2014

120 250 5/31/2014

136 300 4/30/2014

120 350 5/31/2014

136 150 4/30/2014

120 100 5/31/2014

As you can see from the table as loaded in my model, there three columns. The personnel number the amount paid and the date (period), in this case two. I have written a measure that calculates difference between what each per no was paid in the last period (4/30/2014) and the current period (5/31/2014) but where I run into problem is how to determine the following measures:

1. The total number of staff (per no) with lesser pay in the current period (5/31/2014) as against last period with the amount of the difference in the pay.

2. The total number of staff (per no) with more pay in the current period (5/31/2014) as against last period with the amount by which the difference in the pay.

3. The total number of staff (per no) with no pay in the current period (5/31/2014) as against last period with the amount of the difference in the pay.

4. The total number of staff (per no) with no pay in the past period (4/30/2014) as against the current period with the amount of the difference in the pay.

Thank you for your help in advance.

Dear Kasper,

Please pardon my error in the above, this is the accurate table to use. Really sorry.

PerNo Amount Date

123 230 5/31/2014

123 300 4/30/2014

145 125 4/30/2014

120 450 5/31/2014

120 300 4/30/2014

136 350 5/31/2014

You may also want to consider using CUBE functions for constructing a dashboard based upon cell values.

Hi Kasper

I need some help in calculating a DAX measure with time intelligence. Basically I have

-> Fact Table (Which contains Sales date, Order Date, Customer Join date)

-> A Join Date Table (Essentially the customer join date table which holds dates and I have linked this table to fact table)

-> Order Date Table ( A separate table with dates that is linked to Fact table)

I’m trying to create a measure to find out the Sum of Sales for 3 months since a new customer joins.

I have used the following DAX but I’m unable to get the right result

– “calculate([Total Revenue],dateadd(‘Join date Calender’[Join Date],3,Month))”

Appreciate any help to achieve the above.

Thanks

Krishna

Dear Mr. Jonge,

I see that the publish date for this book is July 14, 2014. But it is already available for sale in a paperback edition at Amazon.com in the United States.

But there is NO information about the ‘Table of Contents’, OR the ‘Look Inside’ feature of Amazon.com

I am very interested to get a preview and look at the table of contents for this book. Would it be possible to make that available?

Thanks

I’ll ask the publisher what he thinks.

I apologize Mr. Jonge,

I forgot to mention the name of the book in the previous post/email:

“Dashboarding and Reporting with Power Pivot and Excel: How to Design and Create a Financial Dashboard with PowerPivot”

Hello Kasper,

Not sure if this is the best forum for this issue but the PowerPivot Field List for my instance of PP on Excel 2013 (64 bit) is reverting to the classic Pivot Table Field List, even for new PP data models and worksheets. Wondering if this is a known issue related to the latest Office 2013 patch and/or there is a workaround?

Many thanks,

Ben

Hi Ben,

This is by design in Excel 2013. You can do slicers by using the right click menu, there are no slicers wells any more. You can rearrange slicers by using the Power Pivot tab. Excel owns the field list and we have given them this feedback.

Hope that helps,

Kasper

Hello Kasper,

How can I subscribe for your blog? I do not see any option to subscribe for your posts..May be it’s not appearing to me as I’m accessing your site through mobile..

Thanks

Yes there is a subscribe option on the right. I just added email after your request :).

Hi Kasper,

In my company when we are trying to Sign In from Power Query tab in excel using our company email id we get error, 401 Unauthorized : Access is denied due to invalid credentials. Have you ever got such error? Any idea what could be the reason.

Thanks,

Richa

try clearing the cache of your browser and try again. Hope that helps.

Hi

I have just discovered Power Pivot. Wow! I think this is going to solve a lot of my issues with a dashboard I have created in 2007 (too slow, too cumbersome to maintain etc).

I am trying to replicate my existing dashboard using Power Pivot but cannot calculate financial variance.

My data set is:

cost code, account #, date (month), Type (actual or budget), dollars.

A relationship sets the account as either income or expense.

I have calculated variance using measure 1

=sumx(filter(‘DataAccounts’,’DataAccounts’[Type]=”Budget”),’DataAccounts’[Dollars])-sumx(filter(‘DataAccounts’,’DataAccounts’[Type]=”Actual”),’DataAccounts’[Dollars])

When I display my pivot table, I get 4 columns:

1. date, expense and income as rows

2. actual (dollars and measure 1), budget (dollars and measure 1). In both cases measure 1 = dollars ie – this is not variance. If I include row totals, the total sum of dollars’ and ‘total sum of measure 1′ equal each other and are the correct variance figure.

My pivot table in Excel 2007 shows actual, budget and variance ie- 3 columns only.

What have I done wrong?

Thanks

Hi Kasper

I have been using powerpivot for about 2 years now and have a question about query designer.

to modify a query that connects to an analysis cube, when i go to Table Properties > Design, it worked fine.

But recently, I changed the query text and cannot go back to the Design anymore.

Looked around on the web, but couldnt find a solution, if there is any.

would you be aware of any trick/workaround to get the query design back?

I am using powerpivot 64 bit with office 2013 64 bit.

Thanks in advance for your consideration

- Asit

Power Query -

I need to get a count where DefectStatus exists (“New”, “open”, or “reopen”) without affecting the other types of Defect Status-

I just want to group all new/open/reopen

example

Platform = 123 ABC Status “Open”

Platform = 123 ABC Status “ReOpen”

Platform = 123 ABC Status “New”

then Platform = “123 ABC Status” then count = 1

What is the proper syntax or can you directed me to a how to create this?

Thanks,

Karen

You’ll want to write a condition using the || (logical OR) symbol, as in:

(DefectStatus = “Open” || DefectStatus = “ReOpen” || DefectStatus = “New”)

Usually as the second argument to a Measure := CALCULATE(expr, filter_expr) statement

Hope that helps.

Thank you for your quick response.

I am relevantly new to power query – where do I create the condition?

and what would be the correct syntax within Power Query?

Here is my current PivotData Setup:

let

Source = Table.Combine({TPR,LTE}),

InsertedCustom = Table.AddColumn(Source, “Duration”, each [Closed On]-[Detected On]),

ChangedType = Table.TransformColumnTypes(InsertedCustom,{{“Duration”, type number}}),

FilteredRows = Table.SelectRows(ChangedType, each true)

in

FilteredRows

I am assuming I will need to modify the insertedCustom =…. What would be the correct syntax?

Thanks,

Karen

Disregard last comment – posted by mistake – How do I modify this:

let

Source = Table.Combine({TPR,LTE}),

InsertedCustom = Table.AddColumn(Source, “Duration”, each [Closed On]-[Detected On]),

ChangedType = Table.TransformColumnTypes(InsertedCustom,{{“Duration”, type number}}),

FilteredRows = Table.SelectRows(ChangedType, each true)

in

FilteredRows

to modify the Defect Count to only count the Open, New, or reopen as just 1 when any of them = 1

Hi, I just downloaded the file from the url specified(http://1drv.ms/1miiVPG) and follow your book “How to Design and Create a Financial Dashboard with PowerPivot – End to End” page 23 steps to collect the data. I found the pivot table data for the sum of RevenueAmount of $57928870.16 as specified in the book is different from what was retrieved form the access data which is $58057881.68. I don’t know if I have downloaded the correct version of the data file or not. Please advise.

Jasper hi, Is it possible to create a measure that calculates the MAX Value of 2 or more other measures.

Regards

Andy

Andy,

You could do that through a simple IF() stt. comparing two measures as in,

MAXProdOrDevice:=IF([MaxDeviceID]>[MaxProdID],[MaxDeviceID],[MaxProdID])

where: MaxProdID:=MAX([ProdID]) and

MaxDeviceID:=MAX([DeviceID])

One nice thing is that these two measures do not have to reference columns of the same table.

Chris hi, many thanks fore your fast response, I have five measures from which to find MAX and when I tried to do it in excel ended up with a horrendous formula, which I did not think practical to incorporate into Measure! Grateful for your advice.

Regards

Andy

I’m trying to estimate the size of my tabular environment using your spreadsheet, but the link posted in the blog post below goes to an invalid skydrive location. Can you provide an updated link to access the Excel 2013 version? http://www.powerpivotblog.nl/what-is-using-all-that-memory-on-my-analysis-server-instance/

Kasper, I am trying to log a new question but the captcha keeps failing. Can you help me?

It keeps asking me for 3 – X =0.

It doesn’t accept 3 as the answer!

it does, I just need to approve as well

Hi Kasper

I am looking to create a dashboard based of power pivot. Data will be loaded from SQL Server using a custom SQL script. However, I want to be able to amend this script and refresh the data in the data model as and when I need to. I want to keep the integrity of the existing pivots.

Any suggestions on how I do this? Help appreciated!

Hi Matthew,

Not sure what you are trying to achieve but it generally is best practice to build your model on top of views in the SQL database. That way the “model” stays the same even though the underlying database changes. If you change the model itself the PivotTables will change as well.

Andy,

I would probably do this by creating multiple IF() measures each comparing two of your “base” measures at a time… so two IF’s compare 4 “base”, 3 IF’s needed to compare 6 “base”. Then, creating another IF()measure to compare each two of the IF() measures. This will get you the “value” of the highest “base” measure = maximum value from multiple columns.

I used this to find the MAX value among six measures (three measures in each of two tables)

MaxR1:=MAX(Table1[Random1_1])

MaxR2:=MAX(Table1[Random1_2])

MaxR3:=MAX(Table1[Random1_3])

MAX_R1R2:=if([MaxR1]>[MaxR2],[MaxR1],[MaxR2])

Max_R1R2vsR3:=if([MAX_R1R2]>[MaxR3],[MAX_R1R2],[MaxR3])

and

MaxV1:=MAX(Table2[Values2_1])

MaxV2:=MAX(Table2[Values2_2])

MaxV3:=MAX(Table2[Values2_3])

MAX_V1V2:=IF([MaxV1]>[MaxV2],[MaxV1],[MaxV2])

Max_V1V2vsV3:=if([MAX_V1V2]>[MaxV3],[MAX_V1V2],[MaxV3])

Finally,

MaxOfAll6:=If([Max_R1R2vsR3]>[Max_V1V2vsV3],[Max_R1R2vsR3],[Max_V1V2vsV3])

And to display which column first matches the Max of all 6, I used a SWITCH stt. which hard-codes the names of the column to return if its MAX matches the MAXofALL6 (Not sure how to handle ties, eloquently)

FirstMax:=SWITCH([MaxOfAll6],

[MaxR1],”Random1_1″,

[MaxR2],”Random1_2″,

[MaxR3],”Random1_3″,

[MaxV1],”Values1_1″,

[MaxV2],”Values1_2″,

[MaxV3],”Values1_3″,

“Ties”

)

Chris

I have a traditional date dimension linked via integer to a fact table. (One row for each date in the dimension)

What I notice – whether linking to a Sybase database, or SQL Server Analysis services, is that the drop down list in PowerPivot shows all dates – whether they are in the fact table or not.

The data is accurate – with regards to the dates that actually show in the powerpivot table.

But why the dates that aren’t in the fact table show in the drop down list?

The column is recognized as date (with the list of date filters).

I have tried creating a link using date fields, per web suggestions, but still arrive at the some end result.

Hi Kasper,

I bought a copy of your book recently via Amazon. Is it possible to download a copy of the Workbook(s) that you used to illustrate the Dashboard?

It would add significantly to the experience.

Thanks.

I like your book “Dashboarding and Reporting with Power Pivot and Excel”

It would be very helpful if you are able to share the Excel files which includes the examples in the book. – Please advice

Thanks

Andy,

I was thinking some more about your question, and realized that you could use the CUBEVALUE() function to directly pull measures into your EXCEL workbook, then you can use the regular =MAX(cell_range) function to get the MAX of the MAX’s (pretty much regardless of the number of measures involved.

In 2010, =CUBEVALUE(“PowerPivot Data”,”[Measures].[your_measure]“)

In 2013, =CUBEVALUE(“ThisWorkbookDataModel”,”[Measures].[your_measure]“)

…and, if you have slicers, you can add additional arguments to have those slicer selections respected.

Hope you get a chance to check it out. I think you’ll like it!

Chris, Many thanks for both the ‘IF’ and ‘CUBEVALUE’ solution which appears to be the more elegant and flexible solution. Intrigued by your comments re adding additional arguments to have slicer selections respected, would welcome your comments please as I am enjoying the PPivot learning journey!

Well, for instance…

If I have a Slicer that I want to affect my =CUBEVALUE() formula, I just need to add “Slicer {Name}” as an additional argument to the formula. (where: {Name} = {Name to Use in Formula} value, as shown when I right click on the Slicer and choose [Slicer Settings].

Multiple slicers simply require additional Slicer {Name} entries in the formula.

So, with a Slicer named {ProdCode}, I simply add that to my formula as,

‘=CUBEVALUE(“ThisWorkbookDataModel”,”[Measures].[Total Cases_Shipped]“,Slicer_ProdCode)

Slicer arguments DO NOT need quotes around them.

Now, when I choose specific Product Codes via my slicer, the CUBEVALUE() formula will honor the slicer selections; whereas before, it would have returned the Measure results without any filters.

Other arguments to the CUBEVALUE() function can include specific values of a field, such as for a specific date from your date table, as “[DateTable].[Date].[All].[12/31/12]”

Pretty powerful stuff. But you’ll probably want to use some descriptive labels in your Excel worksheet so the end-user knows what they’re looking at.

Also, check out the Help entry in Excel for the CUBEVALUE function. It’ll give you more details.

I have a pivot table that has multiple connections from 2 SQL database tables. I have tried multiple times to get another users pc setup so that they are able to refresh the Pivot table data. I tried with a direct ODBC connection to the SQL Tables and also Creating an .odc file placed in their “My Datasource” folder.

I tried to refresh the data connection string and it fails saying that not a valid path see administrator.

I am looking for the best solution in setting up a user so that they are able to manipulate the Pivot table. Please keep in mind that the original Pivot was created on 64bit pc and the user is 32bit, would that make a difference?

Hi,

The way I understand it you have native pivottables pointing to SQL using Excel. I would recommend importing the data through PowerPivot then you wont need to set it up with a ODC.

Thanks,

Kasper

I am enjoying your book “Dashboarding and Reporting with Power Pivot and Excel”. I would like know where I could get a copy of the sample files used in the book?

Hi, you can find them here: http://www.powerpivotblog.nl/dashboarding-with-excel-and-powerpivot-example-dataset/

Question related to PowerPivot performance and Excel workbook size.

I’m working with a large database of dental practice data. One of the fact tables has 11 million rows; the workbook is 193mb. I’ve created various measures for each of a rolling 12 months.

Generally, is it more resource and performance efficient to write somewhat repetitive measures in the PowerPivot window or to do essentially the same thing using Cube functions and adjustments to slicer values.

Thank you for your help!

I don’t think there is any difference, measures don’t take any resources on their own same as the cube functions.

Hello Kasper

I have seen you previously have a table which has a date and then all the products which show wtd, mtd, qtd, ytd. I am trying to produce a table where it displays previous week, quarter and year growth from a weekly date, and also weekly and quarter growth from the same period the previous year-if that makes sense..so for example if the date was 1st jan 2014 it would show last week, quarter, and year growth, but also the weekly and quarterly growth from 1st jan 2013. I understand my weeks would have to be numbered to match, and for added confusion my years arw july-june.

Does this make sense?

Hello Jasper

I contacted you on twitter initially, for some advise on a powerpoint issue which I cannot get my head around. I want to get to a position which is similar to the positon on pg 53 in the “Data analysis Expressions in the Tabular BI Semantic Model”- which is by date, then current sales, MTD, QTD,YTD, Last Year, PriorYear MTD, Prior Year QTD.

However i am working with weekly data, which i have numbered, and instead of producing tables with YTD, MTD, QTD I would like to show current week sales (ie 1st jan 2014(numbered week 1)), last 4 weeks sales (from the current week ie 1st jan 2014), last 12 weeks sales (from the current week ie 1st jan 2014), last 52 weeks sales (from the current week ie 1st jan 2014)- as well as comparing growth from the previous year same week (1st Jan 2013 (numbered week 1)), growth between last 4 weeks, and the preious year 4 weeks (same period), previous year 12 weeks (same period)

I hope this is possible, and makes sense?

To add confusion to this issue, my financial years are July-June

i look forward to your resposne, and any assistance you can give

Thanks

Anthony

Anthony, I would recommend making sure you have a separate date table with two columns, one column that contains a week number by year, the other one that contains a week number since the beginning of your date table. You will use the second column to work with in DAX not to display and will be really helpful with your calculations.

Now you can use DAX like

Sales same week 52 weeks ago = calculate([measure], datetable[weeknumberfromstart]=values(datetable[weeknumberfromstart])-52)

Sales same last 52 weeks sales = calculate([measure], datetable[weeknumberfromstart] >= values(datetable[weeknumberfromstart]-52)

&& datetable[weeknumberfromstart] <= values(datetable[weeknumberfromstart]))

Hope that helps and gets you started. These two columns will get you a long way.

Hi,

i have a dimension table with month_key having values (201201,201202,201203…….202011,202012) and month name ( Jan 12, feb 12,……NOV 20, Dec 20) and a fact table with columns (month_key ,measure_types, Amount)

My requirement is to create a power pivot report in which when a user select a month from the filter, the report should display the (selected month+18 ) month’s data against each type . when JAN 12 is selected ,the jan 2012 +18 = june 2013 , month name should be populated with months till june 2013 only .

i tried creating calculated column”END DATE ” in the fact table with dax expression to calculate the 18th monh from the current month as below

month_key END DATE

201201 201306

201202 201307

and thought of filtering the table with month key <= ENDDATE but it is not working as expected. could you please guide me on this ? Is there any time intelligence function that serve the purpose . Iam using excel 2010 ..hence could not do any calculation on the report side also. please suggest .

Thanks in advance

Hi Kasper

Thanks for the helpful response. I thought I would have a go before I responded, and the DAX equations make sense to me.

However i have come across an issue. When I try to put the “Sales same week 52 weeks ago” into a table, the following error comes uo “A table of multiple values was supplied where a single value was expected”

Sales same week 52 weeks ago (With my table names in):

Sales same week 52 weeks ago:=CALCULATE([UnitSales], Dates[WeekSinceStart]=values(Dates[WeekSinceStart])-52)

Have you a solution to this error?

For the second DAX equation when I create the equation in the powerpoint window a yellow error flag comes up with the following statement: “Semantic Error:The Values function expects a column reference for argument ’1′, but a string or numeric expression was used”

The DAX equation with my column names in:

Sales same last 52 weeks sales:=CALCULATE([UnitSales], Dates[WeekSinceStart] >= values(Dates[WeekSinceStart]-52)

&& Dates[WeekSinceStart] <= values(Dates[WeekSinceStart]))

I bet there are simple things to fix these however at present I cannot work them out.

Thanks for the help

Anthony

I have a data model with a list of job advertisers and jobs for six months. I want to create a set of advertisers who didn’t advertise in Jan v a comparison month of June. I need to flag these accoringly so I can then filter them and run some analysis on them.

What I am looking to do is to provide analysis around where the growth in the market has been.

Thanks!