PSPivotTable
PSPivotTable copied to clipboard
jdhitsolutions
Count with Totals
Would it be possible to add the ability to include totals to an outputted pivottable that was using counts? For example, if I were to start with a data block like this assigned to $CSVData.
Construction Batch Schedule Status
------------------ ---------------
-1 Past
-1 Past
-1 Past
-1 Past
1 Past
1 Past
1 Past
1 Past
1 Past
1 Past
1 Past
1 Past
1 Past
1 Past
1 Past
3 Current
3 Current
3 Current
3 Current
3 Current
3 Current
3 Current
3 Current
3 Current
3 Current
3 Current
3 Current
4 Current
4 Current
4 Current
4 Current
4 Current
4 Current
4 Current
4 Current
4 Current
4 Current
4 Current
4 Current
5 Future
5 Future
5 Future
5 Future
5 Future
5 Future
5 Future
5 Future
5 Future
5 Future
5 Future
5 Future
6 Future
6 Future
6 Future
6 Future
6 Future
6 Future
6 Future
6 Future
6 Future
6 Future
6 Future
6 Future
7 Future
7 Future
7 Future
7 Future
7 Future
7 Future
7 Future
7 Future
7 Future
7 Future
7 Future
7 Future
8 Future
8 Future
8 Future
8 Future
8 Future
8 Future
8 Future
8 Future
8 Future
8 Future
8 Future
8 Future
8 Future
9 Future
9 Future
9 Future
9 Future
9 Future
9 Future
9 Future
9 Future
9 Future
9 Future
9 Future
9 Future
10 Future
10 Future
10 Future
10 Future
10 Future
10 Future
10 Future
10 Future
10 Future
10 Future
10 Future
10 Future
11 Future
11 Future
11 Future
And use the command New-PSPivotTable $CSVData -yProperty "Construction Batch" -xLabel "Schedule Status" -Count it returns this.
Construction Batch CURRENT FUTURE PAST
------------------ ------- ------ ----
-1 0 0 4
1 0 0 11
3 12 0 0
4 12 0 0
5 0 12 0
6 0 12 0
7 0 12 0
8 0 13 0
9 0 12 0
10 0 12 0
11 0 3 0
And I would like for it to return the totals along the perimeter so it would read like this.
Construction Batch CURRENT FUTURE PAST TOTAL
------------------ ------- ------ ---- -----
-1 0 0 4 4
1 0 0 11 11
3 12 0 0 12
4 12 0 0 12
5 0 12 0 12
6 0 12 0 12
7 0 12 0 12
8 0 13 0 13
9 0 12 0 12
10 0 12 0 12
11 0 3 0 3
TOTAL 24 76 15 115
My end goal is to replicate something I have online that looks like this.
I have not looked at this project in quite a while so I don't know off the top of my head. I can look into this when my travel schedule slows down.
Thanks a ton, it by far is the easiest method I've found for doing this type of stuff. Another question related to the above post, is it possible to do multiple tiers of grouping? For example, you'll see in my screenshot that the data is pivoting on both Construction Batch and Schedule Status and the counts are actually coming from other columns that I didn't include in the example data.
Another option you might want to look at, since you are working with data as you would in Excel is to install the PSExcel module from the PowerShell gallery.
install-module psexcel
You can generate all sorts of Excel data and files even if you don't have Excel installed. You can learn more on the project's Github repo https://github.com/dfinke/ImportExcel
Yeah I have played with that and it is super strong but the boss wants embedded html reports and I'm not so sure that module can accomplish that the way I have been able to with yours. I could be wrong though, need to look through that module's docs more.