Log In
Register
Home
April 27, 2021
Offline
Hi everybody,
I've been learning to use Power Query and Power Pivot with these great tutorials, thanks. I'm working on a Dashboard which I've been asked to do. The Dashboard doesn't concern projects but the topic is similar so I'm calling them projects for simplicity.
I am stuck trying to do a bar chart (or pivot table) showing total impact over several weeks, of projets for which I have a start date and end date (and duration in weeks if needed). They don't want a gantt chart (which is what I originally suggested, using the Excel project tutorial). My projects are grouped by type in pivot table, and I need to be able to filter these as needed.
I can do my bar chart or pivot table, but it only shows the start date impact.
For example,
Project A starts Week 12, ends Week 16, has an impact of 2 (IMPmetier, medium for users)
Project B starts Week 14, ends week 18, has an impact of 3 (IMPmetier strong for users)
I want weeks 14, 15 and 16 to show total impact of 5 but can't get a measure to do this, I've tried all sorts of functions and am stuck !
Does anybody know what function I can use please (I've tried DATEDIFF and all sorts of others too) ?
My current measure is : IF(ISBLANK(SUM([IMPmetier])),0,SUM([IMPmetier])) that way I get all the weeks on bar chart even if no project.
Many thanks,
Eleanor
ps I Don't have the function SEQUENCE on my Excel.
July 16, 2010
Offline
Hi Eleanor,
Welcome to our forum! It's great to hear you're making use of the techniques you've learnt in my tutorials.
It's very difficult to follow your question without a sample Excel file. I can't tell what the source data is from the charts in the Word doc or how those charts were constructed etc.
If your file contains sensitive information then you can anonymise it, or create a mock-up with some dummy data. We don't need a lot of data, it just needs to illustrate your requirements and have the same structure as your actual data file i.e. if there's a Power Pivot model then the mock-up needs to have this too.
If the file is too big to upload to the forum, you can share a link to the file saved on OneDrive or Dropbox or similar.
Mynda
April 27, 2021
Offline
Thanks for your reply Mynda,
I'm joining my files which should be small enough in size.
Calendar file, DIMtype file, the ones that will move around are METIERS and HUB.
On the METIERS file, as you see there is a start and end date. I've created a measure on that table called TESTDUREE to be able to do the addition I'm trying to do, it's now telling me I have a format problem with the column SEMdebut. Yet I've made sure that is a number format, I even added VALUE to make sure. Still not working, and I don't know if my formula will work anyway !
On the HUB file I only have start dates as each event only lasts 1 day (but I created start / end / durations to be able to compile with METIERS file).
Some HUB dates I use in my formulas are empty, that is normal and the file needs to work in spite of that !
The chart needs to end up like on the TDB_CDR file, but I'm still doing this manually as I have the problem I contacted you about, and also a problem joining the information from the 2 files (which is why I created the DIMtype file, to try and join them properly).....
Any help would be really appreciated !
Thanks again,
Eleanor
July 16, 2010
Offline
Hi Eleanor,
Thanks for sharing your files. Just the file containing the Power Pivot model would have been sufficient as we don't need to refresh any connections to troubleshoot.
That said, I can't follow what the issue is because you explained it in English:
"For example,
Project A starts Week 12, ends Week 16, has an impact of 2 (IMPmetier, medium for users)
Project B starts Week 14, ends week 18, has an impact of 3 (IMPmetier strong for users)
I want weeks 14, 15 and 16 to show total impact of 5 but can't get a measure to do this, I've tried all sorts of functions and am stuck !"
But the file is in French and unfortunately, I don't speak French so I'm not able to translate it.
Also, the TDB_CDR table only contains some cells with formulas and I don't know how the other values were arrived at.
One thing I did fix is to change the table the SommeIMPACTdeploiements measure is attached to, from Metiers to Hub, the relationship warning then goes away.
Let's tackle one problem at a time. Please let me know which measure you're having trouble with and what it's supposed to calculate and what results you're expecting, so I can check my results.
Mynda
April 27, 2021
Offline
Hello Mynda,
My actual dream is to join tables HUB and METIERS to obtain a histogramme with both sets of data.
I'm looking to do, on the histogramme, a sort of GANTT chart, with weeks of the year as columns, and in the columns I need the IMPACT score of each event, (and total per Week of course). The TDB_CDR chart was done manually using information from pivot table HUB but for the METIERS table I had to do the scores manually as I can't get the right formula, and that is the one I contacted you for.
So, for now I have 2 charts, one for HUB and one for METIERS, I'm not having any particular difficulty with the HUB part, because the dates only last one day, so the IMPACT in the columns needs only to appear once, and so that is sorted (I'm using the measure sommeIMPACTdeploiements which I learned from your tutorials to show all weeks even those with no score.
I'm having difficulty with the IMPACT scores on the METIER file :
I can get the IMPACT score to come up on start day Week, in the same way as I do for the HUB file, using measure IMPACTmetier0siVIDE. BUT as this impact will last for the duration of that action, I want to repeat the IMPACT score throughout the action duration (we have a start date and end date which I have transformed into start Week and end Week, with also duration in weeks).
I've tried lots of functions, and I've created the measure called testduree. But no luck as yet.
in the METIERS table these are the columns needed I think :
SEMdeb (start Week), DUREESEM (duration in weeks), semFIN (end Week)
NO_SEM is my Week number from calendar table.
So to sum up and prioritize my wish list :
1. I need to show the METIERS impact scores throughout the duration of each action.
2. I need to represent all the actions, both HUB and METIERS, on one graph /gantt sheet (so that I can have a grand total of impact per Week 🙂 ). Ideally in the same pivot table but even by changing the relationships I've not managed to do so. Perhaps I should combine them in a power query file ?
Thanks once more for your help !
July 16, 2010
Offline
Hi Eleanor,
Thanks for providing further information. I'm still a bit in the dark as to what this data represents, but from what you've explained it sounds like you need to count a score on days where you don't actually have data. e.g. week 1, for start week 10 duration is 17. But you don't actually have 17 records, just one record that tells you there should be 17 days worth of scores.
I think you need to create a row for each day there should be an impact score in your source data, rather than using the duration field, as this has no meaning from a time perspective, which is how you want to visualise the data. e.g. using the week 10 example, you need rows for week 10 through 27 with the scores you want to display in the Gantt chart. As opposed to the one row you currently have. Hope that makes sense.
Lesson 7.08 in the Power Query course explains how to expand data into rows.
Mynda
1 Guest(s)
