Ask your questions here

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

66 Responses to Ask your questions here

  1.  

    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]);
      )

       
  2.  

    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..

       
  3.  

    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

       
  4.  

    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.

       
  5.  

    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

  6.  

    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.

  7.  

    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!

  8.  

    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.

  9.  

    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?

  10.  

    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

  11.  

    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!

  12. 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

     
  13.  

    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

  14.  

    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

       
  15.  

    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

       
  16.  

    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!

  17.  

    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

       
  18.  

    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.

  19.  

    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.

  20.  

    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!

  21.  

    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

  22.  

    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!

  23.  

    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

       
  24.  

    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?

  25.  

    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

  26.  

    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?

       
  27.  

    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

  28.  

    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

       
  29.  

    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

       
  30.  

    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

  31.  

    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…

  32.  

    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

       
  33.  

    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

       
  34.  

    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

       
  35.  

    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

  36.  

    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

  37.  

    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)

  38.  

    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?

  39.  

    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.

       
  40.  

    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!

       
  41.  

    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.

  42.  

    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

       
  43.  

    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.

  44.  

    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

  45.  

    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.

  46.  

    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?

  47.  

    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.

leave your comment