1. 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...
  2. 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...
  3. 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...
  4. 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...
  5. 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...
  6. 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...
  7. 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...
  8. 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...
  9. 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...
  10. 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 :(
  11. 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...
  12. 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...
  13. 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...
  14. 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...
  15. 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...
  16. 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...
  17. S

    Power Pivot Table fields not refreshing

    Is there a finite number of measures that can be created in Powerpivot? I have numerous measures; after refreshing all; some measures disappear from the table fields. I've tried searching by name under the ALL tab. They are simply gone. I can see the measure in the Powerpivot Manage...
  18. V

    Trying to get a Net Sales rollup in PowerPivot

    I have a PowerPivot table similar to the following (obviously thousands more lines): <tbody> P&L Category Amount Something else I may Pivot on Sales 100 A Sales 100 A Discounts 20 A Discounts 30 A Returns 10 C Sales 200 C </tbody> I want to get a summary of Net Sales in a Pivot...
  19. J

    Row Total Incorrect Using SumX

    I have a simple table and am using sumx, but the row total is incorrect. The measure is: =sumx(daily,[Gallons]*[NACVal]/[Acres])/[rows] The output looks like this: <colgroup><col><col span="2"><col><col span="5"><col></colgroup><tbody> <colgroup><col span="2"><col><col...
  20. O

    DataModel tables do not refresh with VBA Thisworkbook.Model.Refresh

    Excel 2016 I have a DataModel that is fed from 9 Excel tables (notPowerQuery). I use VBA to run SQLqueries and dump the results into these 9 tables and then refresh the DataModelwith Thisworkbook.Model.Refresh. However, not all the tables refresh their data and am at a lossas to how to...

Some videos you may like

This Week's Hot Topics