Questions tagged [powerpivot]

PowerPivot is a data mashup and data exploration tool based on xVelocity in-memory technologies providing analytical performance to process billions of rows at the speed of thought.

Filter by
Sorted by
Tagged with
1 vote
1 answer
24 views

Excel Pivot Table showing same count as the first column

I'm a beginner at Excel and am currently practicing pivot tables. The data that I'm dealing with is this table: In the ROW Labels, I have added the field "Quiz and Assignment Mark Range" ...
Swetha Rao's user avatar
0 votes
0 answers
64 views

Power Query SQL bring in cell contents for the where statement

I am trying to build a flexible sql where statement so that my power query can pull the exact data the user specifies. I have an excel worksheet with about 18 cells that the user populates. I use ...
Nicholas Adams's user avatar
0 votes
1 answer
73 views

Create a hierarchy between tables following relationship

I am confused with pivot tables, and it's a recurring issue! Say I have put 2 tables in my data model (classes and students): tables classes and students My model is this one: enter image description ...
jgran's user avatar
  • 3
0 votes
0 answers
77 views

Splitting text values in Power Pivots

In my current non-power pivot method, I have a formula here to try and extract the latest sprint from Jira data: =INDEX(SORT(TEXTSPLIT(AC2,,";")),ROWS(TEXTSPLIT(AC2,,";"))) The ...
ade_m's user avatar
  • 1
1 vote
0 answers
31 views

Using a seperate date for FTD outside of datamodel in excel report

I have a data model named Sanction in the following format. Sl. Date Loc Product Num Amount 1 01-06-2023 Loc1 Pro1 2 5 2 02-06-2023 Loc2 Pro2 5 6 I am ...
Mercurial's user avatar
  • 119
0 votes
1 answer
65 views

Every students' score plus their group score in Excel power pivot

There are students' scores based on their usual performance (scoreName), and group scores based on their group performance (scoreGroup). The total scores equal to their 'scoreName' plus their own ...
likelee's user avatar
  • 113
0 votes
0 answers
53 views

How to change change Excel PivotCache from external source to table if external source includes measures

I've inherited a worksheet that contains an external connection via PowerQuery, which is connected in turn to 20 or so PivotTables and four or five common slicers. Users have complained about speed ...
bigsim's user avatar
  • 1
2 votes
0 answers
129 views

Ragged hierarchies in Power Pivot

In Power BI, one can use the Hierarchy Slicer vis from AppSource to hide items that contain blank values: Is there a feature similar to that, but in Power Pivot? I want to hide the blank values, but ...
emil's user avatar
  • 21
0 votes
1 answer
4k views

How to use Get Data in Excel without loading the data fully in Excel

I have a minor nuisance regarding loading the data into Excel. I load data from a SQL database as follows: first go to the tab Data > Get Data > From Database > From SQL Server Database. ...
Snowflake's user avatar
  • 181
1 vote
0 answers
481 views

Show values in PowerPivot only for total/subtotal

I have a value column coming from simple count() in PowerPivot Data model. Is there any way to show count value only in subtotal/grand total rows, without displaying value 1 in each individual row? I ...
tauruss85's user avatar
1 vote
0 answers
21 views

Create a custom pivot table off two different pivot tables

Suppose you have the following 2 tables from 2 different sources of data Table 1 Table 2 I want to create a custom pivot table off these two tables, which will look like the following Note that the ...
tytds's user avatar
  • 31
1 vote
1 answer
3k views

Power Pivot DAX dynamic Percent of Column

I just started using Power Pivot and Dax. Today I learned the benefits of creating my own measures (counta, etc) vs creating them directly in the pivot table. So I thought I could do the same thing ...
daniellopez46's user avatar
0 votes
0 answers
29 views

Excel Power Pivot Sums are off

For work I am extracting data from a huge .csv file (1.754.488 rows), including revenue numbers for different customers at different months. When I load this .csv file into Power BI and create a ...
mlenz's user avatar
  • 1
1 vote
1 answer
3k views

Excel: How can I create a slicer based on data from multiple columns

I'm using PowerQuery and Power Pivot to analyze some data. I would like to create a slicer based on data from multiple columns. Typically slicers are tied to a single column. The below table is a ...
user27654's user avatar
0 votes
1 answer
447 views

Pivot table with multiple tables

I have what I think should be a fairly straightforward situation. I have a table like this: and I want to plot the counts of start dates and end dates per month, for every month in a sequence (but in ...
David Doria's user avatar
3 votes
1 answer
2k views

Changing font size in Power Query

I have started to fiddle with Power Query a bit and thing that is pretty annoying is size of the font in formula bar and advance editor. Its way to small. Also when you manually enter formulas in ...
IGRACH's user avatar
  • 481
0 votes
1 answer
3k views

Can not display date as MMM-YY in Power Pivot chart

Currently I am working on an excel data model which have several Power Pivot Chart in it. I need to make the date format of the axis become MMM-YY (ex: Mar-19) but can not. Current chart format with ...
Lucas's user avatar
  • 1
1 vote
1 answer
155 views

Distinct Count of Items Having Distinct Count of Other Items

I have data that look like this: What I want is a distinct count of the number of dates which have a distinct count of Person equal to two. The answer should here be one. I am in Excel Power Pivot ...
AndrewMartin's user avatar
0 votes
2 answers
153 views

Excel : list of installed base

Thanks in advance for helping me out! How would you advise me to go from this table: Equipment Desc | Ship To Name ------------------+---------- Small | Bob Small | Dylan ...
Thomas de Montlivault's user avatar
0 votes
0 answers
449 views

How to Add custom Categories to Transaction Descriptions in an Excel Data Model

TL;DR: (How)Can I create a pivot table from a data model in Excel where I can add custom text to that table, and then feed that modified pivot table back into the Data model? Background: I am new to ...
Malco's user avatar
  • 101
0 votes
1 answer
56 views

Change Excel Slicer from "OR" to "AND"

I'm trying to graph the number of people who have a certain skill set, using a slicer. The problem that I'm running into is that the slicer tool, by default, will show me ALL of the records that ...
Austin's user avatar
  • 101
0 votes
1 answer
842 views

Excel Power Pivot Data model Sum of Max date groupings

I have a dataset in Excel that I need to summarize. The source table is in a format like below: Table example The data extends for years and includes a heap more metadata columns. The value in each ...
monkeymagik's user avatar
0 votes
0 answers
42 views

MS Excel. How to analyse dates when they are heading names?

I've been given a data set that consists of hundreds of Stores and almost a hundred dates, below is a small sample. The issue is that dates (mmm-yy) are actually heading names. The reason I see this ...
sonic99's user avatar
  • 51
0 votes
1 answer
68 views

How to make a DAX measure NOT to re-calculate on aggregated levels?

I have a DAX measure which works perfectly on the level 0 POV in the Pivot Table but I was wondering if there is a way to make it aggregate like a regular number when filtering to top level POV (e.g. ...
Arthur's user avatar
  • 169
1 vote
1 answer
242 views

Replace Static Table Data w/ External Source and Update Pivot Automatically (Excel)

st tab is a table with data manually pasted from a file nd tab is a pivot based on the 1st tab Is it possible to replace the 1. st tab with an external data source (load-in the file without pasting ...
Arthur's user avatar
  • 169
0 votes
1 answer
36 views

Excel PowerPivot - Working out percentages for columns in a PowerPivot

For illustration, I have two columns: Col1 | Col2 ------------ A | Y A | Y A | Z B | Y B | Y B | Z My Pivot looks like the following (added Col1 as a Row and ColB as a column) Col ...
pee2pee's user avatar
  • 493
0 votes
0 answers
35 views

Not able to install power pivot add in in excel 2010

This message is showing when i double click on setup. please help me to install it .I have read all the installation instruction . My pc is 64 bit and i have downloaded 64 bit power pivot.
ssrm7_bot's user avatar
0 votes
0 answers
494 views

PowerPivot get the last value recorded for a date

I have a PowerPivot query where I have 3 columns: ID, Date and a Value. Every day is possibly recorded multiple values each on new row. so it looks like: ID Date Value 1 1/1/2019 654594 ...
s3icc0's user avatar
  • 1
-1 votes
1 answer
39 views

Create table comparing data from multiple date periods

In Power BI I've made a table where I'm comparing values from different date periods. For example, I have the following columns: Last 7 Days Last 14 Days Last 30 Days Last 60 Days Lifetime So I'm ...
michael.greenwald's user avatar
0 votes
1 answer
1k views

Struggling to import Oracle tables via Excel Power Pivot

I have an issue where i'm trying to pull some data from an Oracle table via Excel Power Pivot. To connect to Oracle, i used Table Import Wizard in Power Pivot and entered all the required info (...
Charles's user avatar
0 votes
1 answer
1k views

Excel 365 - Pivot tables ungroup dates when refreshed; not an issue in Excel 2016

I have an Excel workbook that is pulling data from MS SQL Server into a PowerPivot data model and then about a dozen pivot tables. These tables contain date information that is grouped by year and ...
5E4ME's user avatar
  • 101
0 votes
1 answer
81 views

Using a slicer to choose one column of a power Pivot table to exibit

I have a pivot table that summarizes a list of properties of many furnaces over a period of time. Each line is a date and each column is a property. I have dozens, and properties, would be unpractical ...
Pedro Silva's user avatar
0 votes
0 answers
555 views

Excel Pivot - Group by year and week

I can group data by YEAR. I can also group it by WEEK. However, I can't seem to group it by YEAR and WEEK. I used https://www.extendoffice.com/documents/excel/1874-excel-pivot-table-group-by-week....
pee2pee's user avatar
  • 493
1 vote
1 answer
176 views

PowerPivot - How do I establish a relationship between a table's columns to another table?

I'm beginning my first PowerPivot, but I'm starting to think my setup is completely wrong, or maybe PowerPivot isn't the right approach. I have two tables: A huge table of wages per week for multiple ...
Dan's user avatar
  • 11
0 votes
1 answer
33 views

Excel: Add 3rd "values" column to show percentage of one column against another

Please see attached - how would I get another column in to show the 3rd column as a percentage of the 2nd column? I tried https://www.contextures.com/xlPivot10.html but couldn't see one that would ...
pee2pee's user avatar
  • 493
0 votes
1 answer
3k views

Difference between two averages in excel power pivot

I have the following data table in excel, showing how many apples i collected in the first 12 hours of the day, and how many oranges i collected in the last 12 hours of the day, for the 1st, 2nd and ...
Steven's user avatar
  • 1
0 votes
1 answer
2k views

PowerPivot returning all values

I am trying to learn with PowerPivot and from what I saw, it would be THE tool to help me reconcile similar data in two different systems. However I cannot seem to get through the basics - I have ...
Eleshar's user avatar
  • 204
0 votes
1 answer
712 views

How to use Power Query to create pivot tables with non-numerical values

The data is transformed into an Excel table and loaded into Power Query. It looks like this: (I don't understand why the table doesn't work. After a few searches I realize that the site does not ...
Nicholas Humphrey's user avatar
1 vote
1 answer
129 views

Combine none-unified data in excel based on row-id?

I have the need to combine different excel sheets of data into one single master file. I have one master file with e-mail addresses and several excel-files from our CRM with data in different ...
Per's user avatar
  • 11
0 votes
0 answers
1k views

Vertical and Horizontal Slicer Boxes

I had to go back to Office 2010 from Office 365 in order to create a PowerPivot table for a user who has Office 2010. I had the thing running on 365 but it would not run on 2010. So I uninstalled ...
jpl458's user avatar
  • 113
1 vote
1 answer
67 views

Calculating differences between two sheets (efficiency issue) [closed]

Current setup to compare history on AR Financial Data: -Two excel sheets with identical headers Range: A1 to AL250,000 -On one of the sheets, we added 13 Columns, which have equations doing ...
urdearboy's user avatar
  • 133
1 vote
1 answer
1k views

Is there a solution to the constant unhandled exceptions that Powerpivot throws in Excel 2013?

Each time I perform certain actions with Powerpivot in Excel 2013 it throws an exception about not being unable to cast a COM object to an interface. This happens most notably every time I try to type ...
ChP's user avatar
  • 314
1 vote
1 answer
416 views

How to do a distinct select with CountRows in excel?

I want to do a distinct select on the field Object_name with the below measure ,how would that be possible in excel ? =countrows(filter(GDPR,GDPR[Object_Type]="Table"))
Lucy's user avatar
  • 11
3 votes
2 answers
315 views

Excel PowerPivot Relationships

I have an issue when using PowerPivot in Excel 2016. After creating a relationship between two tables, and afterwards create a pivot table from the data model, the resulting pivot ignores the ...
Nichlas H.'s user avatar
1 vote
1 answer
33 views

Doing calculation in PowerPivot (Excel 2010) based on two tables

I am on Excel 2010 with PowerQuery and PowerPivot add-ins installed. I have two tables, where: Table1 has amounts denominated in different currencies. Table2 has conversion rates (exchange rates). ...
Tee's user avatar
  • 33
-1 votes
2 answers
2k views

PowerPivot with Excel source file on OneDrive

Hi Could you please help me with a PowerPivot question? The data file in Excel format is of huge size 500M+. It’s the source data and stored on OneDrive. I want to create a Pivot table based on ...
Mark K's user avatar
  • 161
2 votes
2 answers
4k views

Cannot group field in Excel PivotTable

I know this question has been asked before, and the usual answer is "your dates are not all stored as dates!" I highly doubt this is the case here. I'm using PowerPivot which pulls data from a ...
Sultan of Swing's user avatar
0 votes
1 answer
355 views

Why's power pivot measure area grayed out?

Can some one please help me? I am a new user of Microsoft power pivot and while I was working on my data, I realized the measured section is all grayed out and I cant enter formulas there. What am I ...
Mawriz's user avatar
  • 1
0 votes
1 answer
8k views

Cannot Install Power Pivot in Excel 2013 [closed]

I have a Windows 10 Enterprise Citrix machine that has Microsoft Office Excel 2013 Professional Plus installed. From Excel when I click File > Options > Add-Ins I do not see a Power Pivot option. ...
Learning_Learning's user avatar
1 vote
1 answer
103 views

In Excel, is it possible to create a graph for every category of data, from the same table?

I'm using Excel and PowerPivot to import some data from database, wich creates a single table (Sales). Let's say my table Sales has 3 columns: Sales, Month, and Category. There are 4 categories. I ...
Edgar's user avatar
  • 720