Merge tables + add a calculation column

Hi Team,
I have 2 tables with some data (different queries result), with Epic ID as the key of both tables.
I would like to merge both tables into 1 new table + add a calculation column to this table (for example divide value from column X with value from column Y)

How to do this ?

Hi @Oded,

Do you have two different data sources in eazyBI? Or do you have built two different reports in eazyBI?

If both reports have the same dimension “Epic ID” on reports rows, you can merge them into one new report.

  1. Take one of the reports as a starting point and select the measures of the second report as well.

  2. Press on “Save As” to save modifications as a new report.

  3. You can create a new calculated measure in the Measures section to get a ratio between column X and column Y. The expression might look like this:

    CASE WHEN --avoid dividing by zero
      [Measures].[X] <> 0
    THEN
      [Measures].[Y] / [Measures].[X]
    END
    

    More details on calculated measures are here: Calculated measures and members

Best,
Zane / support@eazyBI.com

Thank you @zane.baranovska for the answer
but im not sure my issue was clearly defined, so I will try to be more specific on my problem:

I have to calculate a KPI (the calculated column) based on:
Reported effort on Epic + stories (stories which are linked to the epic) / number of bugs linked to each Epic.

My problem is:
I can do report and get the Reported effort (by filter Epics + stories only)
I can do report and get the amount of Bugs linked to each Epic.
but I can’t get both in the same report (as in the first report, I filter out the bugs and in the second I need the bugs for the amount )
If I could have both in 1 report ( Reported effort + amount of linked bugs) I will add the calculation column accordingly.

Example :
Epic number:#1234
Effort(including stories):120d
Bugs opened : 20
KPI = 6 (120/20)

hope I was more clear now
Any suggestion to get it done is mostly appreciated

Many thanks for your support !

Hi @Oded,

You can specify the issue type within a calculated measure using a tuple construction (more details are here: Calculated measures).
For example, Opened bugs and Effort, you might want to use a tuple of measure and dimension “Issue Type” with specified issue type. The expression for KPI might look like this:

CASE WHEN --avoid dividing by zero
  ([Measures].[Bugs opened],
  [Issue Type].[Bug]) <> 0
THEN
  ([Measures].[Effort],
  [Issue Type].[Story])
  / 
  ([Measures].[Bugs opened],
  [Issue Type].[Bug])
END

If this approach does not work for you, share the report definitions of both reports you are trying to merge.

Thank you @zane.baranovska for the answer
tuple construction is not something we know how to use
We did manage to add a column but it got empty result

We’ll be grateful if you could assist us with the below :

we want to have a new column with will following formula :
KPI - Epic + Stories Effort. Hours spent / KPI - Bugs per Epic. Issues Created
(if possible to make the “Hours spent” to “days spent” even better )

please see below 2 reports definitions:

Epic + Stories Effort Report →
{
“cube_name”: “Issues”,
“cube_reports”: [ {
“name”: “KPI - Epic + Stories Effort”,
“result_view”: “table”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Measures”,“selected_set”:["[Measures].[Issues created]","[Measures].[Hours spent]"],“members”:[]}]},“rows”:{“dimensions”:[{“name”:“Issue”,“selected_set”:["[Issue.Epic].[All Issues by epics]"],“members”:[{“depth”:0,“name”:“All Issues by epics”,“full_name”:"[Issue.Epic].[All Issues by epics]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false,“removed”:true},{“depth”:1,“name”:“ABSA Group”,“full_name”:"[Issue.Epic].[ABSA Group]",“drillable”:true,“key”:“ABSA”,“expanded”:true,“drilled_into”:false,“parent_full_name”:"[Issue.Epic].[All Issues by epics]"},{“depth”:2,“name”:“ABSA-1”,“full_name”:"[Issue.Epic].[ABSA Group].[ABSA-1]",“drillable”:true,“key”:“ABSA-1”,“expanded”:true,“drilled_into”:false,“parent_full_name”:"[Issue.Epic].[ABSA Group]"},{“depth”:2,“name”:“ABSA-65”,“full_name”:"[Issue.Epic].[ABSA Group].[ABSA-65]",“drillable”:true,“key”:“ABSA-65”,“expanded”:true,“drilled_into”:false,“parent_full_name”:"[Issue.Epic].[ABSA Group]"},{“depth”:2,“name”:“ABSA-185”,“full_name”:"[Issue.Epic].[ABSA Group].[ABSA-185]",“drillable”:true,“key”:“ABSA-185”,“expanded”:true,“drilled_into”:false,“parent_full_name”:"[Issue.Epic].[ABSA Group]"},{“depth”:3,“name”:“ABSA-186”,“full_name”:"[Issue.Epic].[ABSA Group].[ABSA-185].[ABSA-186]",“drillable”:true,“key”:“ABSA-186”,“expanded”:true,“drilled_into”:false,“parent_full_name”:"[Issue.Epic].[ABSA Group].[ABSA-185]"}],“bookmarked_members”:[]}],“nonempty_crossjoin”:true},“pages”:{“dimensions”:[{“name”:“Customer Environment”,“selected_set”:["[Customer Environment].[All Customer Environments]"],“members”:[{“depth”:0,“name”:“All Customer Environments”,“full_name”:"[Customer Environment].[All Customer Environments]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false}],“bookmarked_members”:[],“current_page_members”:["[Customer Environment].[All Customer Environments]"]},{“name”:“Epic for KPI”,“selected_set”:["[Epic for KPI].[All Epic for KPIs]"],“members”:[{“depth”:0,“name”:“All Epic for KPIs”,“full_name”:"[Epic for KPI].[All Epic for KPIs]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false}],“bookmarked_members”:[],“current_page_members”:["[Epic for KPI].[All Epic for KPIs]"]},{“name”:“Product. Environment”,“selected_set”:["[Product. Environment].[All Product. Environments]"],“members”:[{“depth”:0,“name”:“All Product. Environments”,“full_name”:"[Product. Environment].[All Product. Environments]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false},{“depth”:1,“name”:“IMEX”,“full_name”:"[Product. Environment].[IMEX]",“drillable”:true,“parent_full_name”:"[Product. Environment].[All Product. Environments]"}],“bookmarked_members”:[],“current_page_members”:["[Product. Environment].[IMEX]"]},{“name”:“Issue Type”,“selected_set”:["[Issue Type].[All Issue Types]"],“members”:[{“depth”:0,“name”:“All Issue Types”,“full_name”:"[Issue Type].[All Issue Types]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false},{“depth”:1,“name”:“Epic”,“full_name”:"[Issue Type].[Epic]",“parent_full_name”:"[Issue Type].[All Issue Types]"},{“depth”:1,“name”:“Story”,“full_name”:"[Issue Type].[Story]",“parent_full_name”:"[Issue Type].[All Issue Types]"}],“bookmarked_members”:[],“current_page_members”:["[Issue Type].[Epic]","[Issue Type].[Story]"]}]},“options”:{“nonempty”:true},“view”:{“current”:“table”,“maximized”:false,“table”:{}},“calculated_members”:[]}
} ],
“calculated_members”: []
}

Bugs amount per Epic Report →
{
“cube_name”: “Issues”,
“cube_reports”: [ {
“name”: “KPI - Bugs per Epic”,
“result_view”: “table”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Measures”,“selected_set”:["[Measures].[Issues created]"],“members”:[]}]},“rows”:{“dimensions”:[{“name”:“Epic Link”,“selected_set”:["[Epic Link].[Project].Members"],“members”:[{“depth”:1,“name”:“ABSA Group”,“full_name”:"[Epic Link].[ABSA Group]",“drillable”:true,“expanded”:true,“drilled_into”:false},{“depth”:1,“name”:“Test”,“full_name”:"[Epic Link].[Test]",“drillable”:true,“removed”:true}],“bookmarked_members”:[]}]},“pages”:{“dimensions”:[{“name”:“Customer Environment”,“selected_set”:["[Customer Environment].[All Customer Environments]"],“members”:[{“depth”:0,“name”:“All Customer Environments”,“full_name”:"[Customer Environment].[All Customer Environments]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false}],“bookmarked_members”:[],“current_page_members”:["[Customer Environment].[All Customer Environments]"]},{“name”:“Epic for KPI”,“selected_set”:["[Epic for KPI].[All Epic for KPIs]"],“members”:[{“depth”:0,“name”:“All Epic for KPIs”,“full_name”:"[Epic for KPI].[All Epic for KPIs]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false}],“bookmarked_members”:[],“current_page_members”:["[Epic for KPI].[All Epic for KPIs]"]},{“name”:“Product. Environment”,“selected_set”:["[Product. Environment].[All Product. Environments]"],“members”:[{“depth”:0,“name”:“All Product. Environments”,“full_name”:"[Product. Environment].[All Product. Environments]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false},{“depth”:1,“name”:“IMEX”,“full_name”:"[Product. Environment].[IMEX]",“drillable”:true,“parent_full_name”:"[Product. Environment].[All Product. Environments]"}],“bookmarked_members”:[],“current_page_members”:["[Product. Environment].[IMEX]"]},{“name”:“Issue Type”,“selected_set”:["[Issue Type].[All Issue Types]"],“members”:[{“depth”:0,“name”:“All Issue Types”,“full_name”:"[Issue Type].[All Issue Types]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false},{“depth”:1,“name”:“Bug”,“full_name”:"[Issue Type].[Bug]",“parent_full_name”:"[Issue Type].[All Issue Types]"}],“bookmarked_members”:[],“current_page_members”:["[Issue Type].[Bug]"]},{“name”:“Internal”,“selected_set”:["[Internal].[All Internals]"],“members”:[{“depth”:0,“name”:“All Internals”,“full_name”:"[Internal].[All Internals]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false},{“depth”:1,“name”:“No”,“full_name”:"[Internal].[No]",“parent_full_name”:"[Internal].[All Internals]"}],“bookmarked_members”:[],“current_page_members”:["[Internal].[No]"]}]},“options”:{“nonempty”:true},“view”:{“current”:“table”,“maximized”:false,“table”:{}},“calculated_members”:[]}
} ],
“calculated_members”: []
}

@Oded

Take the first report “KPI - Epic + Stories Effort” as the basis for the merged report.

  1. On report rows, keep the “Issue” dimension with the Epic hierarchy for representing epics as you already have it.

  2. In the Measures section, define a new calculated measure “Created bugs”. For the calculation use a tuple expression of measure “Issues created” and issue type “Bug”; this construction returns count of bugs even there is an “Issue Type” filter on report pages.

    ([Measures].[Issues created],
    [Issue Type].[Bug])
    
  3. In the Measures section, define another calculated measure to calculate KPI.

    CASE WHEN --avoid dividing by zero
      ([Measures].[Issues created],
      [Issue Type].[Bug]) <> 0
    THEN
     --effor logged on Epics and Stories
      (
        ([Measures].[Hours spent],
        [Issue Type].[Epic]) +
        ([Measures].[Hours spent],
        [Issue Type].[Story])
      ) /
      --divided by created bugs
      ([Measures].[Issues created],
      [Issue Type].[Bug])
    END
    

Tuple constructions are very handy in the eazyBI reports. For more details you may check out:

Best,
Zane / support@eazyBI.com