How do I apply a formula to an entire row?

#1

Hi,

I’m working with a client on a reporting requirement named HESA.

http://34.252.59.80:8000/Link/?shortLink=1g6148os86owzbq41dk591uo5o

Part of the process involves applying percentage splits to colleges based on their Non-residential and residential split. As a result I want to apply a percentage formula as a row against my data, is this possible? This percentage also needs to be defined by the client within the dashboard.

We’re currently competing against a poorly put together excel sheet, and I’m hoping Dundas can provide a seamless process alongside better visualisation!

Any help would be much appreciated.

Best,

Jack

#2

Hi Jack,

Are there any specific credentials we should use to logon into the link you provided?
If I understand your requirement correctly, you should be able to achieve it with a formula. Specifically in order to allow clients to define the percentage within the dashboard I would recommend using a parameter placeholder within your formula which is detailed under the formula documentation.
Here can see an example that is using this concept (and sounds to a degree similar to your case)

#3

Hi Ariel,

The table should look like this:
Landfill Incineration Recycle Reuse Composted
Canterbury College 330 450 620 580 250
Apply 65% split 214.5 292.5 403 377 162.5

However using the formula function I can only apply it to a single “measure” and not an entire row, resulting in a single additional column for one of the measures. I’ve trialled using placeholder parameters to have the desired effect, can you have more than one placeholder parameter within a single dashboard?

Many thanks,

Jack

#4

Thanks for sharing a sample structure of your data table. In this case, what I would recommend is using a data cube to Unpivot the data table using the Unpivot transform. once you do so you should get 3 columns (college name, measure name and measure value). Then you can move on to the metric set level and add a formula that will use the measure value column to calculate the 65% split. Finally, you can transpose the results of your metric set (the transpose result option is located under the metric set edit options).

Unpivot settings:
image

Unpivot data preview:
image

Transposed metric set:
image

1 Like
#5

Thank you Ariel I’ll work through this today, looks like the solution I was after!

Kind Regards,

Jack