Home

BI and Data Analysis

48 Conversations

Latest Activity

Custom List Message Item

Is there a way to set the sorting default to sort "largest to smallest"? Every time I sort it makes me hit the pull down menu because the default seems to be "smallest to largest".

 

Thank you.

43 Views
2 Replies

Hello,

 

AFAIK, there is no way of changing the default sort order. However, the data ribbon shows the AZ command and also the ZA command, so you can sort ascending or desc

... Read More

I have collected data on 4-5 types of indicator in a single excel sheet for 100 organisation. now I want to grade each organisation and give a rank according to this data.  pl suggest

37 Views
1 Reply

hi @Md Ehteshamuddin,

 

Can you explain more.

Hello

I have a table in powerpivot data model two tables linked to each other through a one to many relationship. the link is dne through the key "Role"

In the one side table I have put two colums the first one is the role ( role of a sales representative)

... Read More
217 Views
23 Replies
Hi Imed

When you say you can't implement this do you mean it won't let you join the 2 tables together?

Hello,

I am trying to load a larger list >40K records with a few lookups into Power Query. It works for smaller lists, but fails with following error msg on large lists.

DataSource.Error: SharePoint: Request failed: The remote server returned an error: (500

... Read More
53 Views
1 Reply

Is there any way in DAX to identify were a Dimension Filter has been place in the Pivot Frame?

 

The Issue is I am Trying to "Default" a Filter Selection Via Dax . ie You have a Filter True/False.

I want my Pivot Table to show True Data UNLESS the User Expli

... Read More
75 Views
5 Replies

David,

 

Could you put a slicer in conjunction with  IF HASONEFILTER 

 

 

Measure1

=IF (
    HASONEFILTER ( [Boolean] ),
    SUM ( [Cost] ),
    CALCULATE ( SUM ( [Cost] ), Dat
... Read More

Hi David,

 

Here is quite old article with workaround http://spr.com/setting-default-member-using-tabular-mode-ssas-excel-2013/. On the othre hand I don't think something c

... Read More

Hi, i need connect a web page with excel, but give an error (Invalid API Key / Secret Pair. The web page give me to parameters API Key and secret pass. 

 

Any body can help me??.

 

62 Views
1 Reply
Any body can help me please

Hello, I need some help regarding Power Pivot. First of all, I do have Office 365 Home Edition. Does this version support it or not? If not, which versions support it. Also, what are the steps to enable the add-on and import database file to work on it in

... Read More
66 Views
1 Reply

Hi,

 

Nope, Power Pivot is not available for Home edition, which versions are supported is here https://support.office.com/en-us/article/Where-is-Power-Pivot-aa64e217-4b6e-410b-8337-20b87e1c2a4b

... Read More

Hello,

 

Need help in calculating Horizontal calculation in Power BI.

 

I have data shown in image "ABC", categorized by Months, and Sales amount is filled against Executive ID, in frequency column, I need the count of sales in frequency column (which reflect

... Read More
67 Views
3 Replies
Hello Sergei, Thanks for reply, i believe it will solve my problem. Is there us any other DAX expression to solve this problem, like "horizontal count/sum"??

Hi Gaurav,

 

If the task is to calculate the number of not empty cells in each row, when i use this code

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
... Read More

Hi,

 

How to ADD custom formula for calculation in pivot-table. I'm using "Data Model" in the pivot, See attachment.

144 Views
11 Replies

Hi Gaurav,

 

If you add the table to data model you may use Power Pivot for further analysis. However, isn't  included into all Excel versions https://support.office.com/en-us/article/Where-is-Power-Pivot-aa64e217-4b6e-410b-8337-20b87e1c2a4b?ui=en-US&rs=en-US&ad=US

... Read More

In Excel 2016 Windows or Mac: has anyone been able to get the FORECAST.ETS.SEASONALITY function to return anything other than zero? I've tried this function on several sets of data, some with very obvious seasons, but this function doesn't recognize them.

... Read More
115 Views
5 Replies

Hi Bob,

 

I'm not a specialist in this area, just checked the file which could be downloaded from this support page https://support.office.com/en-US/article/FORECAST-ETS-SEASONALITY-function-32a27a3b-d22f-42ce-8c5d-ef3649269f3c

... Read More

I'm switching over a spreadsheet from using the ATP to using LINEST. Because LINEST doesn't calculate an adjusted R2, my thought was I could just do it manually based on the R2 value LINEST calculates. 

 

However, the Toolpak Adjusted R2 value does not come

... Read More
38 Views
0 Reply

Great new feature (preview) is introduced in Apr update of Power BI Desktop. Now we may connect directly to the dataset published in the Power BI service.

 

Other words – create and support the data model in Excel, and generate visualization in Power BI Des

... Read More
525 Views
14 Replies
Hi Sergei

Interesting approach, any particular reason you wouldn't create the data model in Power BI desktop? Given you have to load it into there anyway in order to publi... Read More

 

On the office 365 plans page a lot of screen space is taken up highlighting (IMHO) fairly low priorty features for small and medium businesses.

 

The fact that none of these plans contains Power Pivot is much more significant and should be flagged as such.

... Read More
327 Views
9 Replies

Thank you for letting us know there is some confusion about how to get Power Pivot, and which versions of Office have it. We've put together a help article at https://support.office.com/article/aa64e217-4b6e-410b-8337-20b87e1c2a4b

... Read More
Best Response

They don't contain some other features as well. More exact comparing is here https://technet.microsoft.com/en-us/library/office-applications-service-description.aspx

I gue

... Read More

 

May 2017 updates for Get & Transform in Excel 2016 and the Power Query add-in

 

Excel 2016 includes a powerful set of features based on the Power Query technology, which provides fast, easy data gathering and shaping capabilities and can be accessed throug

... Read More
68 Views
1 Reply

I am used to seeing Analysis Toolpak in the top right corner of the Data tab in Excel.  It has disappeared. It is loaded according to the Options section in File.  How can I get it back?

186 Views
5 Replies

Hi Steve,

Am assuming you are using Excel 2016.

Anyway, the ATP (Analysis Tool Pack) can be enabled by the following steps:

File>Options>Add-ins........."Manage: Excel Add-i

... Read More

I reappeared today.  I do not understand.

I also have the same problem

Just found what announced few months ago Analyzing Data with Power BI and Power Pivot for Excel (Business Skills) is finally published.

 

So far had no chance to read it all, but on the first glance it will be another must book for everyone who works with D

... Read More
113 Views
1 Reply
Up to chapter 5, useful advice so far

For example,

JustMeausre:=SUM(
	// comment
	JustTable[a] // one more comment
)

doesn't work, only

JustMeausre:=SUM(
	/* comment */
	JustTable[a] /* one more comment */
)

instead. 

Taking into account you practically always within some function single-line comme

... Read More
104 Views
2 Replies

Received an answer on another forum Microsoft added that as a bug into the system, will fix one day

Is PowerPivot for Excel 2013 32 bit compatible with SQL Server 2014 SP1 Enterprise 64 bit ?

140 Views
5 Replies

Given that you are using Exel 2013, you have access to the PowerQuery addin, which will definately allow you to get the data you need. My advice is to create an ODBC conn

... Read More
I have some problem to connect PowerPivot for Excel 2013 32 bit and SQL Server 2014 SP1 Enterprise 64 bit, and I wasn't sure if this configuration is OK, because I find o... Read More
Hi Jean-Claude, in what way do you need it to be compatible?

I need to get external data into Excel from a password protected Access database (of which I know the password) which I would like to run Pivots or Power Pivots from. When I go to "Get External Data" within Excel and select "From Access" and select the db

... Read More
112 Views
1 Reply

Hi Matt,

 

For my knowledge connection to password protected Access database is not supported by G&T. This link is from Power BI, but they use the same with Excel engine 

http://community.powerbi.com/t5/Desktop/How-do-I-connect-to-a-password-protected-Access-Database-Is-this/m-p/143447#M61889

... Read More

I am working in Power BI Desktop and I need to be able to access files that are stored on a network share. I'd like to be able to just click on the link and get the file.
 
I have 2 columns named Folder_Location(UNC path - \\Server\Folder\) and FileName.
 
I

... Read More
181 Views
6 Replies

Hi Guy,

 

For my knowledge that doesn't work so far. Power BI Desktop (as well as G&T with Power Pivot, same engine) converts the text into the hyperlink if only your text

... Read More

This is only since the most recent Power Query update. I've attached a screenshot of what Power Query displays as well as an attachment of the raw data file being consolidated. Please advise.

282 Views
13 Replies

Just a follow up to my earlier response.

 

It looks like the text file is fixed width.  

 

I pulled it in using the following steps (See attached image and file)

 

 

 

Fixed Width Text File.PNG

Note: I di

... Read More

Hi @Cory Barnes,

 

I get the same issue as you whether I use the older version of Power Query or the new one.

 

Can you send a screenshot of the original M code from the Powe

... Read More

Using Microsoft EXCEL. I have a question regarding the ANOVA, two-factor  with replication. I have my data set up such that I have three columns with differnent sample types (labeled above) and 8 rows per sample for two sample groups (labeled on left) and

... Read More
138 Views
0 Reply

Hello Community. I have a quick question to ask the community. I have a data set (attached below) I'm trying to answer a few questions. i need to find a way to make a table that counts the number of times each player_name and team_name had an offensive re

... Read More
93 Views
1 Reply

Hi,

 

Perhaps easiest way is to use Get&Transform.

 

If i understood your logic correctly. Name your first column as ID (in A1), when generate the query on your data (stay on

... Read More

Is there an easy way, function etc, that will allow me to include as part of my report name, the current visible scenario name. The issue is I run multiple scenarions and have to manually change the report title every time I run a scenario? 

 

Your help wil

... Read More
118 Views
3 Replies

Hi James

 

Are you using Scenario Manager?

 

In which case include a cell reference for your heading for each scenarion.

 

e.g. in the screen shot I have added a reference to c

... Read More