Forum Discussion
Summing up files extracted from SharePoint folder
I have imported the contents of a SharePoint folder that contains several Excel files. After import, the contents are extracted and each of these files (and their respective rows of data) show up under the column Source.Name. This column acts as an identifier column so you can identify each data set. So in the Query Editor, the Source.Name column is the first column, followed by all of the additional columns that are common to all of the Excel files.
My goal is to have a measure that sums up the total of the potential for all of the Source.Name files. I could then add them to chart (by month), or put visuals like what I have below side by side. In other words, to replace the big card visual below, using my current method I would have to duplicate the measure below, but change the source file referenced in the measure to the 1-6-20 Excel file. I know there must be a smarter way.
I made a simple measure but it only works if I select a particular source.name file, and only if I filter by the relevant month.
My data model is below.
SumX of Source.Name = CALCULATE(SUMX(Forecasts,Forecasts[Potential]),FILTER(Forecasts,Forecasts[Source.Name]="Top FCST to review 1-27-20.xlsx"))
8 Replies
- parry2k
Super User
Anonymous so if I understood correctly all the excel files are appended together with the file name as source column, correct? So what is not working? What you are trying to achieve?
parry2k - Yes, that is correct.
The goal is to compare beginning of the month forecast, with end of the month forecast, and see what changed. And do this for each month, over the year.
- parry2k
Anonymous so file name tells which is for beginning of the month and which is the last date of the month or there is a date column that identifies start and end of the month
parry2k Sorry, I don't think I answered your "what's not working" question.
As I mentioned, right now that formula only works if, after the = sign, the formula refernces a particular file (in source.name column).
I'm trying to get it to where I don't have to specifically mention a particular file, but can acheive getting to a particular result by using filters/slicers. So, to use the two tables in my original post. I would put two card visuals on the table, with the same measure, and just filter the measure differently for each visual.
