HI there,
I am trying to transpose below table from rows to column.similar to below.I am not using pivot in power Query due to some restriction.
| Month | Rev | Count | Div(Rev/Count) |
| Nov | 18 | 8 | 2.25 |
| Oct | 23 | 7 | 3.285714 |
| Sep | 10 | 6 | 1.666667 |
into table
--------------
| Desc | Value | Month |
| Revenue | 18 | Nov |
| Revenue | 23 | Oct |
| Revenue | 10 | Sep |
| Division | 2.25 | Nov |
| Division | 3.28 | Oct |
| Division | 1.667 | Sep |
| Count | 8 | Nov |
| Count | 7 | Oct |
| Count | 6 | Sep |
But Actual Result i am getting is below(used calculated table formula)
| Desc | Value | Month |
| Revenue | 18 | Nov |
| Revenue | 23 | Oct |
| Revenue | 10 | Sep |
| Division | 18(wrong value) | Nov |
| Division | 23(wrong value) | Oct |
| Division | 10(wrong value) | Sep |
| Count | 8 | Nov |
| Count | 7 | Oct |
| Count | 6 | Sep |
Problem i am facing is that i am not getting the division value correct.
Note that i am not using Pivot method in Edit query .
Steps i followed:
Step 1 :Created a measure name Div in transaction table :
Div = Sum(TranTable[Rev])/SUM(TranTable[Count]) or calculate(Sum(TranTable[Rev])/SUM(TranTable[Count]) ) or calculate(Sum(TranTable[Rev]))/calculate(SUM(TranTable[Count]) ) --Nothing is working here to correct the division formula.
Step2 : Created a derived table using union & Select column with Date key as one of the column and join relationship with Dim date.
(used calculated table formula)Table = union(SELECTCOLUMNS(TranTable,"monthkey",TranTable[timekey],"Revenue","Revenue","Value",TranTable[Rev]),SELECTCOLUMNS(TranTable,"monthkey",TranTable[timekey],"Count","Count","Value",TranTable[Count]),SELECTCOLUMNS(TranTable,"monthkey",TranTable[timekey],"Division","Devision","Value",TranTable[Div]))
This table will have only three column ,one is timekey for relationship , attribute ,Values so that i can easly transpose row to column .which look like below ,Problem i am facing is that Division is not working here ..any suggestion will be of great help!
into table
Revenue Value Month
| Revenue | Value | Month |
| Revenue | 18 | Nov |
| Revenue | 23 | Oct |
| Revenue | 10 | Sep |
| Division | 18(wrong value) | Nov |
| Division | 23(wrong value) | Oct |
| Division | 10(wrong value) | Sep |
| Count | 8 | Nov |
| Count | 7 | Oct |
| Count | 6 | Sep |
--
source tables used
Transaction table :
| timekey | ProductID | Rev | Count | |
| 92019 | 1 | 1 | 1 | |
| 92019 | 2 | 2 | 1 | |
| 92019 | 3 | 2 | 1 | |
| 92019 | 4 | 1 | 1 | |
| 92019 | 5 | 1 | 1 | |
| 92019 | 6 | 3 | 1 | |
| 102019 | 1 | 3 | 1 | |
| 102019 | 2 | 4 | 1 | |
| 102019 | 3 | 3 | 1 | |
| 102019 | 4 | 4 | 1 | |
| 102019 | 5 | 2 | 1 | |
| 102019 | 6 | 4 | 1 | |
| 102019 | 7 | 3 | 1 | |
| 112019 | 1 | 4 | 1 | |
| 112019 | 2 | 2 | 1 | |
| 112019 | 3 | 2 | 1 | |
| 112019 | 4 | 1 | 1 | |
| 112019 | 5 | 2 | 1 | |
| 112019 | 6 | 2 | 1 | |
| 112019 | 7 | 1 | 1 | |
| 112019 | 8 | 4 | 1 | |
Product table:
| ProductID | ProductName |
| 1 | Pro1 |
| 2 | Pro2 |
| 3 | Pro3 |
| 4 | Pro4 |
| 5 | Pro5 |
| 6 | Pro6 |
| 7 | Pro7 |
| 8 | Pro8 |
Time Table
| Timekey | Month | Year |
| 92019 | Sep | 2019 |
| 102019 | Oct | 2019 |
| 112019 | Nov | 2019 |
