Percentage of multiple dimension total

[mefallit]

Hello,

I have 3 different Custom Dimension for XRAY. I’m trying to reach percentage of each project values like Android Done/Total. in this example: 165/264 so %62.5 is done. How can add this to one column?

[nauris.malitis]

Hi @mefallit

For this, you can create a new measure in the Measures dimension that will calculate the percentage value:

CASE WHEN
  [Measures].[Issues created] > 0
THEN
  [Measures].[Issues created]
  /
  (
    [Measures].[Issues created],
    [DimensionFromColumns].CurrentHierarchy.DefaultMember
  )
END

Be sure to use the real Dimension name instead of the [DimensionFromColumns] in the formula and set the Formatting to Percentage.
This formula will divide the current, for example, “Android DONE” issues created by all issues created (including other members from the dimension in Columns).

However, if you have selected only these four to report on (and you have a lot more members in the dimension in Columns), you can build a custom formula like this:

CASE WHEN
  [Measures].[Issues created] > 0
THEN
  [Measures].[Issues created]
  /
  Aggregate(
    {
      [DimensionFromColumns].[(none)],
      [DimensionFromColumns].[Android DONE],
      [DimensionFromColumns].[IOS DONE],
      [DimensionFromColumns].[Web DONE]
    },
    [Measures].[Issues created]
  )
END

​Best regards,
​Nauris