Exclude Some Breakdowns for Formula Indicators

AmberTemple
Tera Guru

Hey team! I'm trying to create a data visualization with a formula indicator, that has 2 breakdowns: Resource and Task. In order to get a percentage out of 100 for each task, I structured the formula so that it looks like this: ([[Total hours]] / {{Total hours}}) *100

I excluded breakdowns from the denominator so that when I apply a grouping in the data visualization, the denominator will stay at 100%. However, I am wondering if there is a way to exclude only one of the breakdowns, and not all. Basically, I want to only exclude the Resource breakdown, so that each resource's tasks still add up to 100% individually, and not that all resource tasks combined add up to 100% (see screenshot).

Thanks in advance!

1 ACCEPTED SOLUTION

@rajumunta Thank you so much for your time and collaboration! I wound up solving this but I did it slightly differently. I essentially created two indicators that both calculate the total number of hours spent, and I added a Resource and Task breakdown on each indicator. So so far, they are configured the exact same. However, for one of the indicators (lets call it indicator B), I added a Breakdown Matrix Exclusion, where I specified Resource as the primary breakdown and Task as the second breakdown. This tells it not to nest these breakdowns together for that particular indicator. After that, I created the formula indicator, structuring it like this: ([[Indicator A]] / [[Indicator B]]) *100 and I made sure to apply both breakdowns to the formula indicator.

Then, in my pivot table visualization, I grouped the rows by Resource, and added a second row grouping of Task.


I just repeated this for the Actual Hours indicator, and added that as a second data source. Here is a screenshot of the final result!

image_720 (2).png

View solution in original post

11 REPLIES 11

Can you post a snapshot(s) of the Scorecard Visualization Setting used and if possible Indicators.

 

Alternatively you can check these:
Breakdown: Resource only

Primary BD: Resource

Secondary BD: Task

Visualization Type: Stacked Bar for 100%

Group by Resource

AmberTemple_0-1753389525177.png

Here is a screenshot of the configuration panel. In this version, I am using the formula as described in my initial post

Unable to view the snapshot. Unfortunately don't have access to my instance. Will share my updates in 2 hours.

AmberTemple_0-1753389525177.png

I am re-uploading the image. Hopefully you are able to view it now! 

@AmberTemple Not sure of the issue but still unable to view the images here. Here is what I am seeing on my side.

1. Created Two indicators first.  Source Table would be the Time Entry or your custom table related to Hours). Aggregate would be Sum of Unit Hours.
Indicator 1: Task-Level Hours (for this I used Assigned & Task Type as Breakdowns as an example). In your case the Breakdowns would be Resource & Task.
Indicator 2: Total resource Hours (for this I used Task Type only). In your case Breakdown will be Resource Only. 

2. Created the Formula Indicator and included both the Breakdowns. So you would be including Resource & Task Breakdowns.

3. Finally Created the Scorecard widget using the Scorecard Visualization type. See snapshot. 

Hope this helps you. If yes, please indicate by selecting ‘Accept as Solution’ and ‘Helpful’. This action benefits both the community and me.