I need to calculate billing percentage with respect to the total in Azure Data Factory WorkFlow. I used the expression:

Total_amount/sum(Total_amount) 

But it doesn´t work. How could I calculate percentages using aggregate transformation inside a data flow?

CodePudding user response：

Create a data flow with 2 sources. They can both be the same source, in your example. The first stream will have Source->aggregate [sum(total amount)]->Sink (cached). The second stream will have Source->derived column (total amount/lookup from the cached sink above). My example screenshot below does this exact same thing with loan data. This is what my formula in the derived column looks like: loan_amnt / sink1#output().total_amount

CodePudding user response：

You can try using windows transformation in data flow.

Source:

Add a dummy column using derived column transformation and assign a constant value.

Using windows transformation, get the percentage value of the total amount.

Expression in window column:

multiply(divide(toInteger(Total_amount),sum(toInteger(Total_amount))),100)

Preview of windows transformation: