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

@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

Glad you were able to resolve it. I was skeptical on the Breakdown Matrix Exclusion and changed my mind since there was no way for me to verify the actual outcome/solution. Awesome on detailing what worked for you as well so that it may be helpful to others.