When you have created a modeled sheet that utilizes custom dimension values, there are a couple of ways that you can get the aggregated values of a single dimension.
Modeling Accounts in Workday: The Formula
When modeling accounts in Workday, you write the formula to only use the dimension value you choose. In this example, we will look to only calculate based on the LoanCategory.
To obtain the Principal Balance for only the loans with a LoanCategory of ‘Commercial Real Estate’, we can write the formula like this, checking that the ROW.LoanCategory = “commercial real estate”.
Then in your cube sheet for the Commercial Real Estate Principal Balance account, simply link to this modeled account.
This works perfectly, but you would need to create a modeled account for each dimension value for the dimension that you are working with.
NOTE: you also have to create your dimension name with no spaces for it to work in the formula.
Using Linked Filters
A better way to do this is to simply use the Linked Filters option from the cube account. When we use the Linked Filter, we don’t need to specify the dimension value we are looking for in the modeled account formula, so it would simply look like this.
Then in the cube sheet, when you select the account to link it to, you also specify the Linked Filter, by pressing the Edit link.
Then you can select the Dimension you are wanting to filter on, then use the dropdown to select the Dimension value to use. (as seen below)
Summary
Both of these options will work the same. In both cases, you will need a separate account in the cube sheet for each Dimension value you are using, but using Linked Filters you will only need one modeled account formula, instead of a modeled account formula for each dimension value.
