Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
instead of just using regular Excel Expression, use DIVIDE Function where you can give numerator, Denominator and if denominator missing alternative as 0. Try that and let me know if you any questions
If you find this as solution please add KUDOs
Proud to be a Super User!
I used all method.. Divide(Num,Denominator) or calculate (Sum(Num))/(calculate(Sum(Denaominator))) or Calculate (Divide (Num,Denominator)) --Nothing is working .. Derived table is not responding at all for Division or "/" function.. however simply putting random number in numerator or Denominator for testing like Num/4 or 5/Denominator giving expected values .. but i want Divide(Num,Denominator) to work expected in Calculated table which is not happening.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
102 | |
84 | |
79 | |
68 |
User | Count |
---|---|
120 | |
110 | |
94 | |
81 | |
77 |