1. G

    PowerPivot CALCULATE SUM

    Hi, would appreciate any help with this, I'm trying to do a calculate sum if in PowerPivot, example of how it workls in Excel below, but am unsure how to replicate in PowerPivot. Thanks in advance for any help. Gav.
  2. G

    PowerPivot Count Duplicates

    Hi, would appreciate any help with this... I have a table of Invoice Data, Including Company Number and Date, I need to count the number of duplicates where a company has more than one invoice for the same day. ( I need to count in PowerPivot itself, as I will be performing more calculations...
  3. G

    PowerPivot Weighted Average

    Hi, would appreciate any help on this, I want to get a weight on groups of rows in my Data using PoverPivot. So for example, the weight by Customer by Product in January. This sort of structure, but obviously a lots more rows ! Is it possible to sum the Amount column by row using variables...
  4. KCRENO7

    Clearing global data source permissions to update Power Pivot, is there another way?

    Hello, I have pulled in data from SharePoint to make my PowerPivot. When I send the reports to others and they go to Data-Refresh, they get the following error message: Microsoft.Mashup.Engine.Interface.ResourceAccessForbiddenException I found this Work-around : go to Get Data/ Data Source...
  5. L

    Filtering a Calculated Measure From Another Table

    Hi All, My brain is fried again. I have scenario where I have to calculate various stats between two sets of data. The only thing common between the two data sets are the Calendar Weeks and the Supplier Numbers. So I have two tables: Table 1 has all of the supplier claims quantities by...
  6. J

    % calculation with different total for each level of aggregation

    Good morning everyone. In my Excel file I have an "Orders" table which among other things includes the field "Hours needed", which is basically the time in hours required to complete the order and the "Date of order" which is the date when the order was carried out in dd/mm/yyyy format. You can...
  7. L

    Difficulties in creating many-to-many relationships in PowerPivot

    Hey guys, I am currently trying to build a database in Excel that contains all Green Climate Fund (GCF) projects. Recently the GCF published an API that allows internet users to access their project data through the following URL: https://api.gcfund.org/v1/projects. I used the "Get Data from...
  8. D

    Import to Power Pivot..

    Hello, I'm exploring Power Pivot (Power Query, BI) opportunities. I have some 24+ workbooks with IDENTICAL data structure in data sheet and 100K+ rows each. I use a pivot in each workbook. My goal is to have one "power pivot" with all the data (+ additional small reference tables) so I can work...
  9. F

    PowerPivot, Running Totals, and Chart Lines with null data

    Good afternoon all, I'm certain that we've all had this issue at one point or another but I can't seem to find a solution--hoping that someone has something ready to go. Here's the summary: PivotChart with Date values on X AXIS and a Running Value Count 'flattens' when max is reached. The...
  10. W

    Distinct values - Powerpivot

    I've been doing some work to identify some issues with the data in a couple of my database tables. I've got one table [tbl_customer] with a reference number (unique) and details of customers, and another [tbl_sales] with details of the products that they've purchased (many products and many...
  11. J

    Extract unique value from column using DAX

    I've come across what I thought should be a simple problem, but I can't quite figure it out. I have a table that's the result of an expression that could have a column like this in certain instances: Row Key Index <tbody> AH4000 1 AH4000 2 AP9999 3 </tbody> What I want is to keep only...
  12. J

    PowerPivot - Consolidating identical data sets from two companies

    Hi everyone, I'm new to this forum and hoping to get help with an issue I've been trying to resolve for a few hours now. The company I work has two entities with separate sets of accounting books in QuickBooks, the US-based accounting software. I'm trying to combine identical data sets from...
  13. J

    Table with multiple date columns

    Hey, together I have the following table in Excel, which I add in PowerPivot to the data model: <tbody> ID Created Started Finished 1 01.01.2019 01.01.2019 05.01.2019 2 02.01.2019 03.01.2019 08.01.2019 3 03.01.2019 03.01.2019 04.01.2019 4 03.01.2019 04.01.2019 08.01.2019 5...
  14. B

    Powerpivot, similar formula to Countif ?

    Hi , i have a Datatable imported into Powerpivot, where i need for 6 columns a conditional count for each row, my formula in Excel is similar to =COUNTIF([Q1]:[Q6]),"") I have big issues doing the same in Powerpivot, can anyone help? - i tried with COUNTX to no avail :(
  15. M

    Struggling with large Powerpivot

    I have a large data set - 1,600,000 rows. I am using powerpivot so I can use all the data set. The pivot table itself however is still limited by the 1 Million lines. Most of the time this is not an issue. However I have one report where I want to display in the pivot itself more than the limit...
  16. D

    Questions before diving in a cash management model using powerpivot and DAX

    Hi there, I'm refreshing/improving a cash management tool / dashboard I developed last year and I'm looking into information to do it the right way. The environment is as follow : - group has several companies and multiple bank account per company - some account are checking accounts and some...
  17. K

    Dynamic DAX calculation between two rows of same DAX measure (filtered rows)

    Background: I have a measure that calculates Total sales from a master sales data that captures actual sales and forecasted sales on a monthly basis. Sample data: And a sample looks like below: So, the June cycle and July cycle are actual sales upto Jun and Forecasted Sales from Jul to Dec...
  18. W

    Subtotals show grand total

    Trying to create a measure that an end user could grab and not have to worry about filtering to get the numbers narrowed down appropriately. Sum of gl transactions measure below. Works, but end users would have to know how to manipulate the filters on Acct Fin Class Code, Acct Prop Class...
  19. A

    PowerPivot drill down capabilities

    Hello, if I understand this correctly, Power Pivot is only good if one works with large data. So, I cannot bring data into excel (after I do connection) other than using Power Pivot? If I drill down, will it only bring 1000 lines? If that is the case, what's PowerPivot is good for? Doing just...
  20. P

    SQL Query on PowerPivot Data Model through VBA?

    Hi all - I'm in the process of developing an MI dashboard for a set of business users, and I often get requests from these users for basic reports for the granular data. I want to build a self service reporting tool within this dashboard; The solution that I envisage is a worksheet with form...

Some videos you may like

This Week's Hot Topics