Expand my Community achievements bar.

SOLVED

How can "Calculated Metric" not affect the Total Sum?

Avatar

Level 1

Hi all, I am trying to make a calculated metric which only applies to "Site Code = FR"

jongchanh269234_0-1702519847020.png

And it works correctly in this way.

However, the problem is it also applies to the Total Sum.

 

Please find the attached image below. 

It should applies only to "B", not "A".

 

jongchanh269234_1-1702519988489.png

 

Could you help me to fix this or suggest better approach?

 

Thanks in advance!

Topics

Topics help categorize Community content and increase your ability to discover relevant content.

1 Accepted Solution

Avatar

Correct answer by
Level 1

Hi Jenifer,

Thank you for the replies.

we used another calculated metric, which is

 

[Metric]  -   (Segment condition [Metric] x 0.4)

In this way, we could solve the issue.

 

Thank you!

View solution in original post

3 Replies

Avatar

Community Advisor and Adobe Champion

I don't know if that is possible (at this time)... The total looks at all the data, and despite the equals value, basically when looking at all the data, somewhere in all the data, there are values that equal "fr", so therefore, it applies.

 

It's little things like this that sometimes make things challenging when using Workspace to pull reports... it would be nice if we had more control over how that "total" works, like being able to show total, or average, or when doing an If statement like your example, to show the de-duplicated total of the values from the rows rather than applying the calculation to the total itself... 

 

However, do you need the total? Or is the problem just that the value causes confusion? If I do something like this, I will often just hide the total (if I can't get the right value, don't show it all at).

 

To hide the total, you can deselect "Show totals" using the gear on the column.

Jennifer_Dungan_0-1702531627794.png

 

 

If you need the adjusted totals, this is really tricky..

 

If you were looking at something like occurrence or page views (something that you can add without worrying about needing to de-duplicate the values), you could re-do this report easily in Excel using Report Builder, cause you can just sum the adjusted rows... 

 

I suppose with some complex logic you could do this in Report Builder for Visits with a few extra requests...

 

To try and get a de-duplicated total, I suppose you could create segments for Visits that only contain "fr", (you can apply the * 0.4 to the total number of visits), and then use another segment to pull Visits excluding "fr" only visits, (basically exclude the first segment), which should be your remaining visits which you can take at face value...

 

With a third pull with the language breakdown, you can then use Excel to translate the pulled breakdown data, and apply the * 0.4 to the FR row, then use the two "total" pulls from above to create a "Total" for the modified breakdowns...

 

So in theory:

  • Visits that only contain "fr" - might return 7,000,000
  • Visits excluding "fr" only visits - should then return 308,088,670
  • To get the new total, you would take 308,088,670 + (7,000,000 * 0.4) = 310,888,670
  • Then under that total, you would show all your rows, with the FR row adjusted

 

I know neither of these is ideal... 

Avatar

Correct answer by
Level 1

Hi Jenifer,

Thank you for the replies.

we used another calculated metric, which is

 

[Metric]  -   (Segment condition [Metric] x 0.4)

In this way, we could solve the issue.

 

Thank you!

Avatar

Community Advisor and Adobe Champion

I'm glad you figured out a solution