powerpivot

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

    Only show Subtotal and not the detail within rows (Powerpivot)

    Hi, Is it possible to create a measure that will only be used as a sub-total. I have a field in my table called "Present", the data for this is allocated to the top row for the daily records.When this is pivoted all I want to show is the sub-total and not the detail for that particular row...
  18. D

    Sharing/Permissions on PowerPivot Workbooks

    I'm very new to PowerPivot and the underlying data structure, so forgive me for missing any obvious answers here. I'm replacing a manually updated workbook with a data model and PowerPivot features and ultimately going to be sharing a dashboard-like file with external users. I want them to be...
  19. S

    DAX Powerpivot Formula Lastdate

    Hi Guys Im looking for some help on a formula in a powerpivot to bring back the latest date for each Customer I am currently using =Maxx(filter(query,query[Customer]),query[Last_sale_date]) but doesn't seem to work Any advice Regards Soph
  20. P

    PowerPivot & Excel Row Level Security - How secure is this solution?

    Afternoon All, Before I begin, I know that you can't implement true RLS in excel - but due to pricing, Power BI is a non-starter. These excel models will also not be shared outside of my organisation. The solution I'm using is obtaining the windows username via VBA, pasting it into a cell/table...

Some videos you may like

This Week's Hot Topics

Top