How to add multiple measures into a pivot table?

1
votes

I'm quite new to using pivot tables and data models, so I don't even know if what I want to do is possible. I have a pivot table (PivotTable1) and its source (Table 25) and I would like to add a hundred or so measures which are listed in the TableCombinations.

enter image description here

For example, I entered the two first measure in orange, but they are not linked to TableCombination and entering them all one by one would be quite long. Each measure is for a distinct Sum wfn column that sums all other rows multiplied by a coefficient. The TableCombinations table simply states the coefficient to be used for each column. For the first three rows, these are my measure formulas :

sum wf1=1.4*Table25[Sum of wD]+0*Table25[Sum of wL]+0*Table25[Sum of wS]+0*Table25[Sum of wW]+0*Table25[Sum of wWSOUL]
sum wf2=1.25*Table25[Sum of wD]+1.5*Table25[Sum of wL]+1*Table25[Sum of wS]+0*Table25[Sum of wW]+0*Table25[Sum of wWSOUL]
sum wf3=1.25*Table25[Sum of wD]+1.5*Table25[Sum of wL]+0*Table25[Sum of wS]+0.4*Table25[Sum of wW]+0*Table25[Sum of wWSOUL]
...

Two questions :

  1. Is there a way to link the tables so that any change made to TableCombination would then be updated in the pivot table measures?
  2. Is there a way to generate all the of the measures without typing them in one by one.
2
excelpivot-tablepowerpivot
The word that you are looking for is dynamic range - this is the hint for first question – Prasanna

2 Answers

2
votes

You should be able to use just one DAX measure to do this, using the CROSSJOIN function.

Don't set up a relationship between the Tables, and drag # to the Columns area of the PivotTable. Then create this Measure:

=SUMx(CROSSJOIN(Table1,Table2),Table1[wD]*Table2[wD]+Table1[wL]*Table2[wL]+Table1[wS]*Table2[wS]+Table1[wW]*Table2[wW]+Table1[wWSOUL]*Table2[wWSOUL])

That should give you the exact answer you need.

Here's how it looks using some sample data:

enter image description here

...and here's the sample data I'm using:

enter image description here

0
votes

You could certainly use VBA to add measures, and to update them when the Table changes. I might have a crack at writing up an answer along that approach shortly. But here's another way to achieve what you want.

I've previously written some code to slave a Table to a PivotTable, so that any change in the PivotTable's dimensions or placement will be reflected in the shadowing Table's dimensions and placement. This effectively gives us a way to add a calculated field to a PivotTable that can refer to something outside of that PivotTable. If the PivotTable grows, the Calculated Table will grow. If the PivotTable shrinks, the Calculated Table will shrink, and any redundant formulas in it will be deleted.

You can easily use this approach to perform your calculations in a 2nd table alongside your PivotTable, and each column x in that 2nd table could easily reference row x in your 'parameters' table.

See Select Newest Record and Create New Table of Unique Values in Excel