Pivot Table with the same value with different field settings

[gglgiggles]

I want to recreate the below pivot table, but cannot figure out have to have the same value with different field settings.

What I have tried is: $excel=$tbl| Export-Excel -StartRow 1 -WorksheetName 'Deciles' -TableName 'Table' -TableStyle 'Light1' -Path $outfile -AutoSize -PivotTableName 'bySegDec' -PivotRows @('segment_acronym','decile2') -PivotData @{score=@('Min','Max','Avg')} -PivotColumns @('mail_hold') ` Any advice would be appreciated.

[dfinke]

Try

-PivotData @{scoreMin='Min';scoreMax='Max';scoreAvg='Avg')}

[gglgiggles]

When I added

               -PivotData @{scoreMin='Min';scoreMax='Max';scoreAvg='Avg';scoreCnt='Avg'} 

Creating Excel File WARNING: Problem adding data fields to PivotTable bySegDec. WARNING: Problem adding data fields to PivotTable bySegDec. WARNING: Problem adding data fields to PivotTable bySegDec. WARNING: Problem adding data fields to PivotTable bySegDec.

I will also need to add them to the Column groups as well.

[dfinke]

can you post a simple repro. data and script.

[gglgiggles]

Script needs to be renamed (remove .txt) Pivot_Table.ps1.txt

How I would like it to look:

1. Totals on Rows not present
2. Values contain Min, Max, Average, and Count of Score
3. Columns should be Mail_Hold and values.

I have included the output I would like to have

PivotTables.xlsx

[dfinke]

i was over optimistic. there is no implementation for the same dimension with different measures.

also for the PivotColumns where you want to specify a name for a row and the measure. no implementation. tbh, this is the first time i have seen that done, and i've been using Excel forever. 😆

i like both ideas, would be cool to have.

don't know when or if i'll have time to figure out the level of effort to implement.

[gglgiggles]

Thank you for looking into it. I will continue to create it with just the minimum and add the others as needed until something more permanent can be done.

[dfinke]

i'm guessing you can do it with powershell.

using -PassThru you get the excel object model back.

you can find the pivotable and add to the fields.

if you search for Excel VBA and pivottables you can probably find how it can be done. then approximate the properties and methods to do the updates you need.

$excel=$tbl| Export-Excel -StartRow 1 
-WorksheetName 'Deciles' -TableName 'Table'
-TableStyle 'Light1' -Path $outfile
-AutoSize -PivotTableName 'bySegDec'
-PivotRows @('segment_acronym','decile2') -PivotData @{score=@('Min','Max','Avg')}
-PivotColumns @('mail_hold') -PassThru

$excel # < set  a breakpoint

Close-ExcelPackage $excel

[dfinke]

One step of a multi step workflow

this creates multiple measures for the same field.

it is brute force, looks brittle and doesn't provide the full solution.

also passing a hastable to PivotData and having the key be the field name will need to be changed. then there is the PivotColumns, similar challenges.

it a fun puzzle and you are the only person to ask for this in a decade 😁

$excelParams = @{
    Path         = $xlfile
    AutoSize     = $true
    PivotRows    = @('segment_acronym', 'decile2')
    PivotData    = @{score = 'Min' }
    PivotColumns = @('mail_hold')
}

$xl = $data | Export-Excel @excelParams -PassThru
$pt = ($xl.Workbook.Worksheets | Where-Object { $_.pivottables }).pivotTables[0]

$df = $pt.DataFields.Add($pt.fields["score"])
$df.Function = "Max"
$df.Name = "Max of Score"

$df = $pt.DataFields.Add($pt.fields["score"])
$df.Function = "Average"
$df.Name = "Average of Score"

Close-ExcelPackage $xl -Show

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.