New Topic

Community Topic Idea Report Builder

David Registro

APAC SD Team

Mark McGuire

Yellowfin Support Team

Assigned

Eric Hagfors

Yellowfin Support Team

Category Report Builder

Product Version 7.4

Add a Percent of Total Row Totals Option

Fabian Koenig shared this idea ago

Idea Logged

When building a cross-tab report, if you set a Metric's Aggregation to Average, then the Column totals no longer make sense in terms of using a Percent of Total Advanced Function:

/4624a43d4ea4cac2e29b881a13711242

As you can see above, neither the Row or Column Totals are correct.

If you use SUM, it correctly calculates the Percent of Total at the column level, but it also SUM's the row values, which provides for undesired values:

/e5c3508e605549aaf1ac079a8751cec1

Instead for the Row Totals column, it'd be nice to have an option for using Percent of Total there

I like this idea 0

Replies (5)

Mike Sheehan ●

Hi Fabian,

Thank you for your Idea. I've gone ahead and logged this as an enhancement request in our internal tracking system. Any potential updates regarding this will be posted here.

Regards,

Mike

Reply URL

Fabian Koenig ●

Thank you very much, Mike!

Very much looking forward to this feature being considered as a future enhancement to Yellowfin.

I think in order to properly calculate the percentages of total in the TOTAL columns/rows one would have to be able to specify the following in the Advanced Functions options:

across which dimension the totals should be computed (i.e. what amounts to 100%). In the example above:
- whether it is by the years, thus the rows would amount to 100%, showing the proportion each year contributes to the total (100%) across all years within each demographics group and for the overall total
- OR whether it is by the Camp Demographics, thus the columns would amount to 100%, showing the proportion each demographics group contributes to the total (100%) across all demographics groups within each year and the overall total.

Thank you & Best Regards

Fabian

Reply URL

Mike Sheehan ●

Hi Fabian,

Thank you for your feedback. I've made note of this in the internal task.

I'll keep you posted.

Regards,

Mike

Reply URL

Peter Ruth ●

Hi Mike,

Has this request progressed at all? I have a client that would like to do this also.

Regards,

Peter

Reply URL

Eric Hagfors ●

Hi Peter,

This is Eric filling in for Mike as he is away this week. I checked on the developer task for updates, and it looks like the priority on this Enhancement request has been raised to High recently. I've added your organization as an affected client to the task, and asked for a status update on your behalf. Will keep you updated here.

Thanks, Eric

Reply URL

Jaromir Folders ●

Pending a solution for above issue, a work around solution could be the following:

Use a custom-function to calculate a total for a specific dimension (see example below for sql server).

Applying it to the example above, you can create a calculated field (e.g. Total Invoiced per Year) using the custom-function and select the Invoiced Amount as a measure and the Year (dim) as the dimension.

Then you can make another calculated field (% of Total Invoiced Amount) where you divide invoiced amount by the calculation "Total Invoiced per Year". (use a case when to prevent dividing by zero: CASE WHEN Total Invoiced per Year <> 0 THEN SUM(Invoiced Amount) / Total Invoiced per Year END).

You have to drag both calculated fields in your report.

You can hide "Total Invoiced per Year".

Both must have Totals on and for the % Total field it must be a calculated total.

Hope this helps

Please note that the custom-function uses a window function. Not all databases support these functions.

<name>Total by Dimension, SQL Server</name>

<name>Measure</name>

<datatype>numeric</datatype>

</argument>

<name>Dimension field</name>

</argument>

<sql>

<![CDATA[

SUM(SUM( $1)) OVER (PARTITION BY $2)

]]>

</sql>

<database>SQLServer</database>

<return>numeric</return>

</function>

URL

Mike Sheehan ●

Thanks Jaromir!

URL