Hey there,
I couldn't attach the images to the post so I have a google drive link of some jpgs.
Basically I can do what I need in Access via a query with group lines and a sum of the total for each software title - the total needed for a Monday.
In Excel I have a data model and can make a calculated total but I am not sure how to group the software license down so for a Monday there is one line per software title and the sum of the use for all the groups that need it.
Sorry I find it hard to explain what I need but hopefully the images will help.
Please as me to clarify anything.
https://drive.google.com/open?id=1ErpagGt0nqEkIsvEub2qc5fOH38IWakg
I have an issue where if I add a calculated column in tblCrewPlan there is one line per craft per Monday. i.e. Surfacing 30/03 Count of 10. BUT, when I do the join, Surfacing uses 2 different pieces of Software so the calculated column would return two lines, which of course breaks the calculated column.
Is there a way to build an output 'table' or list or something as a result of these calculation.
Thanks
Hi Steven,
Last time I looked at this I showed you how to use Power Query to calculate the number of licences in the 'CrewPlan' query. What happened with that? I ask because Power Query is where you should be doing these calculations, rather than Power Pivot.
Mynda
Yeh I have that now.. just merged the tables together in Power Query, so I have a single table now on a sheet.. per the chunk below.
| Craft | Date | Count | tblCraft.Craft | tblLicenseUsage.License | tblLicenseUsage.Usage | Total |
| Art and Design | 30/03/2020 | 2 | NA | NA | 0 | 0 |
| FX | 30/03/2020 | 2 | FX | Houdini FX | 0.6 | 1.2 |
| FX | 30/03/2020 | 2 | FX | Houdini Core | 0.4 | 0.8 |
| Surfacing | 30/03/2020 | 10 | Surfacing | Houdini Core | 1 | 10 |
| Surfacing | 30/03/2020 | 10 | Surfacing | Mari | 1 | 10 |
| Editorial | 30/03/2020 | 1 | NA | NA | 0 | 0 |
| Storyboard | 30/03/2020 | 0 | NA | NA | 0 | 0 |
| Modeling | 30/03/2020 | 3 | NA | NA | 0 | 0 |
| Rigging | 30/03/2020 | 4 | NA | NA | 0 | 0 |
| Matte Painting | 30/03/2020 | 0 | Matte Painting | Nuke | 1 | 0 |
| Matte Painting | 30/03/2020 | 0 | Matte Painting | Houdini Core | 0.5 | 0 |
| Lighting | 30/03/2020 | 2 | Lighting | Nuke | 1 | 2 |
| Tracking | 30/03/2020 | 0 | NA | NA | 0 | 0 |
| Layout | 30/03/2020 | 5 | NA | NA | 0 | 0 |
| Animation | 30/03/2020 | 10 | NA | NA | 0 | 0 |
| Roto | 30/03/2020 | 0 | Roto | Nuke | 1 | 0 |
| Compositing | 30/03/2020 | 0 | Compositing | Nuke | 1 | 0 |
| DI | 30/03/2020 | 0 | NA | NA | 0 | 0 |
| Crowd | 30/03/2020 | 0 | Crowd | Houdini FX | 0.6 | 0 |
| Crowd | 30/03/2020 | 0 | Crowd | Houdini Core | 0.4 | 0 |
| Art and Design | 30/03/2020 | 11 | NA | NA | 0 | 0 |
| Editorial | 30/03/2020 | 4 | NA | NA | 0 | 0 |
| Storyboard | 30/03/2020 | 0 | NA | NA | 0 | 0 |
| Modeling | 30/03/2020 | 14 | NA | NA | 0 | 0 |
| Rigging | 30/03/2020 | 10 | NA | NA | 0 | 0 |
| Surfacing | 30/03/2020 | 14 | Surfacing | Houdini Core | 1 | 14 |
| Surfacing | 30/03/2020 | 14 | Surfacing | Mari | 1 | 14 |
| Matte Painting | 30/03/2020 | 1 | Matte Painting | Nuke | 1 | 1 |
| Matte Painting | 30/03/2020 | 1 | Matte Painting | Houdini Core | 0.5 | 0.5 |
| Tracking | 30/03/2020 | 0 | NA | NA | 0 | 0 |
| Layout | 30/03/2020 | 11 | NA | NA | 0 | 0 |
| Animation | 30/03/2020 | 13 | NA | NA | 0 | 0 |
| FX | 30/03/2020 | 5 | FX | Houdini FX | 0.6 | 3 |
| FX | 30/03/2020 | 5 | FX | Houdini Core | 0.4 | 2 |
| Lighting | 30/03/2020 | 8 | Lighting | Nuke | 1 | 8 |
| Roto | 30/03/2020 | 0 | Roto | Nuke | 1 | 0 |
| Compositing | 30/03/2020 | 0 | Compositing | Nuke | 1 | 0 |
| DI | 30/03/2020 | 0 | NA | NA | 0 | 0 |
| Crowd | 30/03/2020 | 2 | Crowd | Houdini FX | 0.6 | 1.2 |
| Crowd | 30/03/2020 | 2 | Crowd | Houdini Core | 0.4 | 0.8 |
| Art and Design | 6/04/2020 | 3 | NA | NA | 0 | 0 |
| Editorial | 6/04/2020 | 1 | NA | NA | 0 | 0 |
| Storyboard | 6/04/2020 | 0 | NA | NA | 0 | 0 |
| Modeling | 6/04/2020 | 3 | NA | NA | 0 | 0 |
| Rigging | 6/04/2020 | 4 | NA | NA | 0 | 0 |
| Surfacing | 6/04/2020 | 10 | Surfacing | Houdini Core | 1 | 10 |
| Surfacing | 6/04/2020 | 10 | Surfacing | Mari | 1 | 10 |
| Matte Painting | 6/04/2020 | 0 | Matte Painting | Nuke | 1 | 0 |
| Matte Painting | 6/04/2020 | 0 | Matte Painting | Houdini Core | 0.5 | 0 |
| Tracking | 6/04/2020 | 0 | NA | NA | 0 | 0 |
| Layout | 6/04/2020 | 5 | NA | NA | 0 | 0 |
| Animation | 6/04/2020 | 10 | NA | NA | 0 | 0 |
| FX | 6/04/2020 | 4 | FX | Houdini FX | 0.6 | 2.4 |
| FX | 6/04/2020 | 4 | FX | Houdini Core | 0.4 | 1.6 |
| Lighting | 6/04/2020 | 2 | Lighting | Nuke | 1 | 2 |
| Roto | 6/04/2020 | 0 | Roto | Nuke | 1 | 0 |
| Compositing | 6/04/2020 | 0 | Compositing | Nuke | 1 | 0 |
| DI | 6/04/2020 | 0 | NA | NA | 0 | 0 |
| Crowd | 6/04/2020 | 0 | Crowd | Houdini FX | 0.6 | 0 |
| Crowd | 6/04/2020 | 0 | Crowd | Houdini Core | 0.4 | 0 |
| Art and Design | 6/04/2020 | 10 | NA | NA | 0 | 0 |
| Editorial | 6/04/2020 | 4 | NA | NA | 0 | 0 |
| Storyboard | 6/04/2020 | 0 | NA | NA | 0 | 0 |
| Modeling | 6/04/2020 | 14 | NA | NA | 0 | 0 |
| Rigging | 6/04/2020 | 10 | NA | NA | 0 | 0 |
| Surfacing | 6/04/2020 | 14 | Surfacing | Houdini Core | 1 | 14 |
| Surfacing | 6/04/2020 | 14 | Surfacing | Mari | 1 | 14 |
| Matte Painting | 6/04/2020 | 1 | Matte Painting | Nuke | 1 | 1 |
| Matte Painting | 6/04/2020 | 1 | Matte Painting | Houdini Core | 0.5 | 0.5 |
| Tracking | 6/04/2020 | 0 | NA | NA | 0 | 0 |
| Layout | 6/04/2020 | 11 | NA | NA | 0 | 0 |
| Animation | 6/04/2020 | 13 | NA | NA | 0 | 0 |
| FX | 6/04/2020 | 5 | FX | Houdini FX | 0.6 | 3 |
| FX | 6/04/2020 | 5 | FX | Houdini Core | 0.4 | 2 |
| Lighting | 6/04/2020 | 8 | Lighting | Nuke | 1 | 8 |
| Roto | 6/04/2020 | 0 | Roto | Nuke | 1 | 0 |
| Compositing | 6/04/2020 | 0 | Compositing | Nuke | 1 | 0 |
| DI | 6/04/2020 | 0 | NA | NA | 0 | 0 |
| Crowd | 6/04/2020 | 2 | Crowd | Houdini FX | 0.6 | 1.2 |
| Crowd | 6/04/2020 | 2 | Crowd | Houdini Core | 0.4 | 0.8 |
| Art and Design | 13/04/2020 | 3 | NA | NA | 0 | 0 |
| Editorial | 13/04/2020 | 1 | NA | NA | 0 | 0 |
| Storyboard | 13/04/2020 | 0 | NA | NA | 0 | 0 |
| Modeling | 13/04/2020 | 3 | NA | NA | 0 | 0 |
| Rigging | 13/04/2020 | 4 | NA | NA | 0 | 0 |
| Surfacing | 13/04/2020 | 10 | Surfacing | Houdini Core | 1 | 10 |
| Surfacing | 13/04/2020 | 10 | Surfacing | Mari | 1 | 10 |
| Matte Painting | 13/04/2020 | 0 | Matte Painting | Nuke | 1 | 0 |
| Matte Painting | 13/04/2020 | 0 | Matte Painting | Houdini Core | 0.5 | 0 |
| Tracking | 13/04/2020 | 0 | NA | NA | 0 | 0 |
| Layout | 13/04/2020 | 6 | NA | NA | 0 | 0 |
| Animation | 13/04/2020 | 10 | NA | NA | 0 | 0 |
| FX | 13/04/2020 | 4 | FX | Houdini FX | 0.6 | 2.4 |
| FX | 13/04/2020 | 4 | FX | Houdini Core | 0.4 | 1.6 |
I need to be able to line graph this also based on the license having the Data as one axis and the total as the other.
I tried to Pivot the column Craft and using total as the breakout for the data underneath to end up with a column for each of the licenses, but I cant graph it for some reason.
I have multiple things like this now all almost working but all failing at the last step, like the running totals previously.
Hi Steven,
Like the chart in the attached file (I assumed you meant 'date on one axis' not 'data')? Note: you probably want to exclude NA from the chart, but I wasn't sure if this represented a craft or meant N/A.
Mynda
ok I think my issue is I am trying to make a pivot based on the Power Query results. I am keeping the power query active as when I add things to the underlying tables used in the merge the power query updates nicely.
Issue was when I used the table the power query made as the source, the dates only showed as Qtr and year in the pivot, it lost knowing it was a weekly amount.
It's weird, the data I pasted above was from the power query table - is there a way with a table that is a live power query to be able to made to a pivot that actually keeps the dates as per column B?
| Sum of Total | Column Labels | |||||
| Row Labels | Houdini Core | Houdini FX | Mari | NA | Nuke | Grand Total |
| 2020 | 1653.1 | 772.8 | 910 | 0 | 1180 | 4515.9 |
| Qtr1 | 31.5 | 5.4 | 24 | 0 | 11 | 71.9 |
| Mar | 31.5 | 5.4 | 24 | 0 | 11 | 71.9 |
| Qtr2 | 469 | 148.2 | 312 | 0 | 215 | 1144.2 |
| Apr | 130.4 | 28.2 | 96 | 0 | 46 | 300.6 |
| May | 139.6 | 42 | 96 | 0 | 57 | 334.6 |
| Jun | 199 | 78 | 120 | 0 | 112 | 509 |
| Qtr3 | 559.3 | 272.4 | 298 | 0 | 409 | 1538.7 |
| Jul | 162.4 | 67.2 | 96 | 0 | 112 | 437.6 |
| Aug | 211.7 | 103.2 | 114 | 0 | 153 | 581.9 |
| Sep | 185.2 | 102 | 88 | 0 | 144 | 519.2 |
| Qtr4 | 593.3 | 346.8 | 276 | 0 | 545 | 1761.1 |
| Oct | 187 | 105.6 | 88 | 0 | 152 | 532.6 |
| Nov | 232.3 | 133.2 | 108 | 0 | 213 | 686.5 |
| Dec | 174 | 108 | 80 | 0 | 180 | 542 |
| 2021 | 311 | 216 | 137 | 0 | 376 | 1040 |
| Qtr1 | 311 | 216 | 137 | 0 | 376 | 1040 |
| Jan | 163.6 | 108 | 76 | 0 | 188 | 535.6 |
| Feb | 147.4 | 108 | 61 | 0 | 188 | 504.4 |
| Grand Total | 1964.1 | 988.8 | 1047 | 0 | 1556 | 5555.9 |
Hi Steven,
You can change the level of grouping of dates in the PivotTable, or remove them altogether, by right-clicking the date field in the PivotTable > Group.
Mynda
