Hi all, I am wondering if there is a way to make a division to ALL column with a constant. By example:
One approach could be to create for every month a variable (using CALCULATE), but I would like if is possible to try to get in one pass… any ideas? (I was thinking in TRANSPOSE and CALCULATE)
Regards,
Miguel
Calculate only works on 1 column. I think Transpose then Calculate would only work if there was only 1 row. So I think you will have to do a Calculate for each column.
The above example with Gather and spread, bring all values in in one column, then the calculation is done and it is transformed back with spread in the original format, but it need to add a „help“ row number column which is removed later on. It has some steps but it saved to add n calculations for n columns.
Hi,
@Admin, @Olaf solution works and here is solution with data screen shots.
Transform file.
Divideall.transform (4.0 KB)
Hi,
If you like you can add a constant for each row, that is used for the calculation.
Add on to the solution by @Olaf
Transform file.
DivideallwithConstantforeachRow.transform (4.0 KB)
Thanks to @Admin @Anonymous @Olaf. It worked really well. I realized that I need to familiarize myself more with Gather and Spread.
As a Tableau user, I was unable to do that in Tableau (you need Prep, and I do not have a license). I can do that in Excel, but I was looking for a way to use an automatic workflow, with your explanations I am able to do that.
I really appreciate a gem as EDT and a very supportive community of users,
Again,
Thank you very much
Miguel
