Calculated element for each of 300 columns

#1

I need to do a calculated element for each of 300 columns. There are 300 brands in my data with rows of scores for each, and I need to work out percentages, percentiles and other calculations for each one.

Obviously I don’t want to have to manually create 300 calculated measures, so I was thinking of using a Python transform to take all the data and loop through. Problem is that I would have to create 300 placeholders.

Am I missing something? How would you do it?

Thanks

#2

Maybe something like pivot/apply calculated element/repivot?

Otherwise python is the way I would go, but what would the placeholders be? There might be a way to make is more generic.

#3

Was thinking of that, but it is also complicated.

I would like python to just take everything - column names and all - and then I can work it out from there.

#4

You could have a python input step that actually queries the data as well, but there again you would need to specify manually all the column names.

If you are talking one such dashboard, maybe you could actually update the dashboard programatically via the rest API. Adding one calculated element, looking at how it is done in the json, and repeat it via some scripting.

#5

Hi David,

You can try using a Transpose Transform that would convert your rows into columns and vice versa and then use a calculated element for your calculation. You can find more information on transpose transform in the link below:

https://www.dundas.com/Support/learning/documentation/data-metrics/transforms/other/transpose

Another suggestion would be to perform these calculations in your database and then connect your data to Dundas BI for further operations.

I hope it helps.

Regards,

Razi

#6

Hi Razi

Thanks for this.

So if I do a transpose, my calculated element will need to work based on all the columns for a particular row. So I will need to do columnA + columnB etc, and there will be 3000 columns as there are 3000 rows to start.

Not sure this is helpful.

Doing the calculation in my database means I need to start working out bridging filters, as these calculations have to happen after filtering.

I may just look into scripting the creation of calculated elements in the cube…

#7

If you look into scripting and python, maybe https://github.com/Web-Power/pydundas could help.
This is a module I built because I am using more and more the API and I am extremely lazy and do not like to repeat myself. Because it’s based on my needs only it is far from complete, but it might still give some ideas or samples.