Custom total with one mesure and columns

#1

Hello,

I have this data, where I have my measure separated in two columns and I want to do a total for each row substracting the two columns(something like $Incoming$ -$Outgoing$. I tried using the custom total option in the measure, but I don't know how to reference the two different columns, it only let me choose the measure $amount$.

How can I do this?

Image title
USsF6u6pQUS7Tq59Jsnw_metric.PNG758×483

And also, another cuestion is, Can I reference this grand totals from other metric in the same dashboard? (to use for other calculation) How can I do that ?


Thank you

#2

one way to solve this would be to seperate your amount measure into 2 measures (Incoming and Outgoing) at the data cube level using the Pivot transform. Then you wouldn't need to use the type dimension on the columns, instead you would just drag your 2 measures and reference both in your formula.

#3

For your second question:


Can I reference this grand totals from other metric in the same dashboard? (to use for other calculation) How can I do that ?


Yes, you can get the grand-totals and the totals for every column in another metric set in the same dashboard. For this, you will have to create a formula visualization. You can use a formula similar to the one mentioned below to get the grand total of the measure irrespective of any column hierarchy SUM($tableScriptName.Corresponding Metric Set.Measure$) and if you want this total with respect to any hierarchy then you can use SUM($tableScriptName.Corresponding Metric Set.Measure$,$tableScriptName.Corresponding Metric Set.Hierarchy$)

#4

Yes, That's what I ended up doing. I wanted to know if there was another way to do it, but that worked.

Thanks.

#6

Hi Carolina,


In order to filter the values of formula visualization as well, you will have to link the filter to the formula visulization as well:


Image title


This will filter the formula visulaization, whenever the original metric set is filtered:


Image title
TQO9VlBATYSaC5M3APkA_FilterResults.PNG249×593


Try this, hope this helps.

#7

Ok, I'll try that. I thought that because it is a formula based on some fields (measures) if the fields change the formula automatically would change.

Thank you very much..