cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Prodigy
Post Prodigy

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"))

 

expected.png

 

data model.png

8 REPLIES 8
Highlighted
Super User IV
Super User IV

Re: Summing up files extracted from SharePoint folder

@texmexdragon 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?






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Post Prodigy
Post Prodigy

Re: Summing up files extracted from SharePoint folder

@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.   

Highlighted
Super User IV
Super User IV

Re: Summing up files extracted from SharePoint folder

@texmexdragon 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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Post Prodigy
Post Prodigy

Re: Summing up files extracted from SharePoint folder

@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.  

Highlighted
Post Prodigy
Post Prodigy

Re: Summing up files extracted from SharePoint folder

@parry2k   I do not have a start or end date column  (although that was suggested in the tutorial I got the idea from).   

 

https://spr.com/power-bi-quick-tips-dealing-with-snapshot-data/

 

To use my examples, the 1-6-20 is the date that someone extracted data from our CRM system  (i.e. the beginning of the month).  

 

The 1-27-20 data was the last time they ran the same report at the end of January.     The goal is to compare what changed between those two files (relative to only the month of January...because each of those files actually has forecast data for the entire year...but I'm only interested in what each forecast snapshot says relative to the month the report was ran.    Unfortunately we don't have a good automated way to do this.   

Highlighted
Post Prodigy
Post Prodigy

Re: Summing up files extracted from SharePoint folder

Sorry, one more thing! There is a date column (tied to the date table) that is called Est.Close Estimated Close represents the date the salesperson thinks the deal will ship.    (no idea why we called it that...but that is what it means).   

 

So, we are comparing all of the opportunities at the beginning of the month, with the opportunities on the report at the end of the month, based on estimated close dates relative to that month.  

Highlighted
Super User IV
Super User IV

Re: Summing up files extracted from SharePoint folder

@texmexdragon This is what I would recommend, slap sample data of few months in excel sheet (few rows) and also expected result and share it through one drive/google drive. Remove any sensitive information before sharing it.

 

You solved one part to append all files together, after that it is a matter of putting DAX measures together and for that need to see the data.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Post Prodigy
Post Prodigy

Re: Summing up files extracted from SharePoint folder

Thanks @parry2k 

 

I have redacted my original file.    Hopefully the enclosed file will help you, help me!  😁

 

https://drive.google.com/open?id=1rqoTuUJMz32yXOzUasZnFRSY9npNRFIN 

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Kudoed Authors