cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shashijss1 Frequent Visitor
Frequent Visitor

Division doesnot work properly in DAX in power BI.Need to transpose rows to column

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.

MonthRevCountDiv(Rev/Count)
Nov1882.25
Oct2373.285714
Sep1061.666667


into table

--------------

DescValueMonth
Revenue18Nov
Revenue23Oct
Revenue10Sep
Division2.25Nov
Division3.28Oct
Division1.667Sep
Count8Nov
Count7Oct
Count6Sep


But Actual Result i am getting is below(used calculated table formula)

DescValueMonth
Revenue18Nov
Revenue23Oct
Revenue10Sep
Division18(wrong value)Nov
Division23(wrong value)Oct
Division10(wrong value)Sep
Count8Nov
Count7Oct
Count6Sep

 

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

RevenueValueMonth
Revenue18Nov
Revenue23Oct
Revenue10Sep
Division18(wrong value)Nov
Division23(wrong value)Oct
Division10(wrong value)Sep
Count8Nov
Count7Oct
Count6Sep

 

--

source tables used 

Transaction table :

timekeyProductIDRevCount 
92019111 
92019221 
92019321 
92019411 
92019511 
92019631 
102019131 
102019241 
102019331 
102019441 
102019521 
102019641 
102019731 
112019141 
112019221 
112019321 
112019411 
112019521 
112019621 
112019711 
112019841 

 

Product table:

ProductIDProductName
1Pro1
2Pro2
3Pro3
4Pro4
5Pro5
6Pro6
7Pro7
8Pro8

 

Time Table 

TimekeyMonthYear
92019Sep2019
102019Oct2019
112019Nov2019
2 REPLIES 2
Highlighted
VijayP Regular Visitor
Regular Visitor

Re: Division doesnot work properly in DAX in power BI.Need to transpose rows to column

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

shashijss1 Frequent Visitor
Frequent Visitor

Re: Division doesnot work properly in DAX in power BI.Need to transpose rows to column

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.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,627)