"Add calculated" Feature - Cumulative sum of percentage of total

Questions & Answers
1

Hi all,

the add calculated feature is really great.

For example I am using it to show a dashed line, that shows the Cumulative Sum of the percentage of total issues, which looks like this:

2020-07-24 11_00_41-IssuesPrivate eazyBI
2020-07-24 11_00_41-IssuesPrivate eazyBI1123×571 39.5 KB

My question: I did this by adding the organde marked “Percentage of total” first, although I do not really want that to be displayed, I only need the dashed line.

Is there a way to edit the Cumulative Sum measure, to not need the first “Add calculated” bars for the percentage of total?

Maybe it can be done, by editing the added calculated measure and include the percentage of total somehow?
By default the cumulative sum is defined like this

CASE
WHEN CurrentTuple(VisibleRowsSet()).Item(0).Name = ‘$total_aggregate’ THEN
[Measures].[Issues resolved %].Value
ELSE
Sum(
Head(VisibleRowsSet(), Rank(CurrentTuple(VisibleRowsSet()), VisibleRowsSet())),
[Measures].[Issues resolved %]
)
END

Thank you in advance and best regards, Markus

2

Hi @markusd,

Yes, standard calculations are great. In most cases saves some stress by creating custom calculated measures, and those are localized to the particular report.

Standard calculations like “Issues resolved %” refer to measures that are predefined or user-defined, or available in the report. In this case, the standard calculation “Cumulative Issue resolved %” refers to another standard calculation “Issue resolved %” that is stored only in the report. And when “Issue resolved %” is removed from the report, it is not available anymore (unknown measure to eazyBI).

You may create a new user-defined measure “Cumulative Issue resolved %” and write the expression on how to get the result from measure “Issues resolved”, not “Issues resolved %”. The formula might look like a combination of standard calculations “Cumulative Issue resolved %” (as the base for the calculation) and “Issues resolved %” with few adjustments.

CASE
WHEN CurrentTuple(VisibleRowsSet()).Item(0).Name = '$total_aggregate' AND
  --and add a condition to avoid dviding with zero
  NOT IsEmpty([Measures].[Issues resolved]) THEN
  --replace "Issues resolved %" with expression referring to "Issues resolved"
  [Measures].[Issues resolved] / SUM(VisibleRowsSet(), [Measures].[Issues resolved])
WHEN NOT IsEmpty([Measures].[Issues resolved]) THEN
  Sum(
    Head(VisibleRowsSet(), Rank(CurrentTuple(VisibleRowsSet()), VisibleRowsSet())),
    --replace "Issues resolved %]" with expression 
    CASE WHEN [Measures].[Issues resolved] = 0 THEN 0
    WHEN NOT IsEmpty([Measures].[Issues resolved]) THEN
      [Measures].[Issues resolved] / SUM(VisibleRowsSet(), [Measures].[Issues resolved])
    END
  )
END

Best,
Zane / support@eazyBI.com

2 Likes
3

Hello Zane,

it works like a charm, awesome thank you so much for the perfectly prepared code :star_struck: :raised_hands:t4:. I would not have been able to create it myself. Really great, thank you :+1:t4:

2020-08-17 16_41_10-Issues - 2 ATC Test Account Small Cube - Private eazyBI
2020-08-17 16_41_10-Issues - 2 ATC Test Account Small Cube - Private eazyBI1178×550 39.9 KB

Best regards, Markus

1 Like
4

Thanks it works perfectly

5

hi，
the data import from JIRA on epic level have 2 status: “successful” and “successful with WA”, i want to calculate Cumulative Sum “successful” of the percentage of (“successful” + “successful with WA”) in each month, tried to defined but failed, could you help on this?

summary %
summary %1340×93 55.3 KB

6

[quote=“mao123123, post:5, topic:4916”]
hi，
the data import from JIRA on epic level have 2 status: “successful” and “successful with WA”, i want to calculate Cumulative Sum “successful” of the percentage of (“successful” + “successful with WA”) in each month, tried to defined but failed, could you help on this?example for table in above post, and chart needed below for your referencechart