1 2 Previous Next 17 Replies Latest reply on Nov 10, 2017 5:12 PM by Yuriy Fal

# Problem with special calculation

Hallo everyone,

I am very desperate because I can not create a presumably complicated calculation. I already suspect that my project is not feasible.

I attached an example. I want to create a calculation that determines the number of projects in each quadrant. Once this works, this calculated number should appear in the background of each quadrant, as shown in the following picture (created with PowerPoint):

You would save my weekend, if you cold solve this task or mention a hint!

Andreas

• ###### 1. Re: Problem with special calculation

Hi Andreas. Here is what I was able to do:

• ###### 2. Re: Problem with special calculation

To put summary numbers behind the chart, you would need to hack a solution using a dual axis. (You would create a second chart, and layer it behind the first.)

The trick is getting the positioning consistent, and placing a zero if needed.

You might use use a second data source that just contains coordinates for the four quadrants, and blend that with the project totals.

• ###### 3. Re: Problem with special calculation

Sorry - I missed the second part of your question, which was how would you get each of those numbers in the background of the proper quadrant. Other than what Branden Kornell suggested above, I can't think of a way to get the quadrant totals placed appropriately. My first instinct was to create a separate worksheet filtered to each quadrant value (i.e. High Risk / High Volume, High Risk / Low Volume, etc), display the total in a circle, and place that as a floating object over the matrix in a dashboard. Unfortunately the transparency can't be set to allow both the matrix and the total to show through.

• ###### 4. Re: Problem with special calculation

Hi, Andreas

Please find my workaround to your case, though it is not meet all your requirement, you may consider as a temporary way before you could get any further solution.

Here is the result

To achieve above,

the first step is to union your data by itself

and then create below calculation fields

and then drag the calculation fields to the viz

and finally, change to dual axis and sync the axis

The workbook attached for your reference. Hope this could help

ZZ

• ###### 5. Re: Problem with special calculation

HI Andreas, ZZ

Sorry for jumping in.

I did this without duplicating data, but one difficulty is align X-axis with whole number and decimal .

I tried to put big circle in the middle of range, but most of the case, the position is not the center, because I needed to put on top of existing data.

Anyways,

Some of calculation involve my over complication and I am lazy not to clean up those..

failure example

Thanks,

Shin

.

1 of 1 people found this helpful
• ###### 6. Re: Problem with special calculation

Hi friends,

I beg your pardon for jumping in with the view

slightly deviating from the original request.

Hope it could help.

Yours,

Yuri

2 of 2 people found this helpful
• ###### 7. Re: Problem with special calculation

Yuriy Fal , Shinichiro Murakami , you guys are legends.

ZZ

• ###### 8. Re: Problem with special calculation

Hallo everyone,

I've never seen such a perfect community before!

Many of your suggestions are fine for me but especially the last one exactly hits my requirements!

Thank you very much!

• ###### 9. Re: Problem with special calculation

Hello everyone again,

I was able to transfer the solution by Yuri Fal to my worksheet but now I am struggeling with a difference between the example data and my original data.

In my original data I assign each project to a project category (one project category includes one or more projects).

Actually I wanted to visualize only the project categories and not single projects. The problem is, that I cannot adjust your solutions, so that it sums up the project volume and only shows the project category.

I updated my example and attached it again and I really hope, that it's not that complicated to transfer the solution to the updated data.

Thanks a lot for your help,

Andreas!

• ###### 10. Re: Problem with special calculation

Hallo Yuriy Fal,

maybe you're again able to help me?

Thank you,

Andreas!

• ###### 11. Re: Problem with special calculation

Hi Andreas,

One question to make things clear to me

Do you want to count Projects in each Quadrant,

or do you want to count Project Categories?

Yours,

Yuri

• ###### 12. Re: Problem with special calculation

Hello Yuriy,

sorry for my late reply! I want to count project categories.

Thanks a lot,

Andreas

• ###### 13. Re: Problem with special calculation

OK, then the next questions, please:

1) What level of detail (VizLOD) should be on a view?

Should each Mark represent a distinct [Project Name],

or should it be a distinct [projekt_category]?

2) How the [Project Risk] value should be calculated

when aggregated by the [projekt_category]?

Should it be weighted by [Project Volume],

or aggregated by some other calculation logic?

Yours,

Yuri

• ###### 14. Re: Problem with special calculation

Question 1: Each mark should represent a distinct [project_category].

Question 2: Sorry, there was another mistake with my example data. In my original data I've got a mapping, so that every project within a [project_category] has the same [project_risk]. Therefore no special calculation logic is needed. I attached the corrected example data.

Yours,

Andreas

1 2 Previous Next