Normally, PivotTables are used to present data in a certain order. For this specific "issue" I need to present numbers and a target. This conflicts in Pivots as the percentage of column takes into account the target as well.
No worries, with PowerPivot it should be possible to address this issue. So far I have been able to create a table with the following layout:
1 2 3
cat A 5 10 7
cat B 10 8 9
cat C 0 2 1
Where 1, 2 and 3 are the first three days of a month (for sake of simplicity rest is left out).
I can get totals of the columns as follows:
= Table.FromRows({List.Transform({"1","2","3"}, each List.Sum(Table.Column(prevStep, _)))}, {"1","2","3"})
Furthermore I am able to divide each value of a column by a number:
= Table.TransformColumns(prevStep, List.Transform({"1","2","3"}, each {_, (this) => this / 42, type number}))
Now I would like to replace 42 with the totals as calculated previously for the columns columns. Note that "{"1","2","3"}" will be calculated automatically in another step.
Can someone elaborate how to achieve this? Expected result:
1 2 3
cat A 0.33 0.5 0.41
cat B 0.67 0.4 0.53
cat C 0 0.1 0.06
@Marc Pince; thanks for your suggestions. After some struggeling I was capable to cook the following:
First, I swapped the total sum calculation line as follows:
totMinDay = Table.ToRows (Table.FromRows({List.Transform(daysOfMonth , each List.Sum(Table.Column(prevStep, _)))}, daysOfMonth )){0},
Where daysOfMonth are the days of that actual month, obtained elsewhere.
Next I calculate the percentage as follows:
perc= Table.TransformColumns(prevStep, List.Transform(daysOfMonth , each {_, (this) => Number.Round(this/ ( totMinDay {Number.FromText( _ ) - 1} ), 3), type number})),
Here I use the fact that the index of the sum is based on the day of month minus 1 (starting from zero) providing solid results.
Thanks for all inputs.