Is there a formula that will produce the same result as setting Sum By Dimension in the visual

Fred Kaffenberger-Oracle

We have a requirement to show reports in constant currency and USD.

In the Financials - AP Payments subject area, I have added exchange rates on the Currency dimension.

My calculation is
Payments Amount CD: SUM( Payments Amount * Exchange Rate BY Ledger Currency )

I'm using Payments Amount from Facts - Ledger Currency and Ledger Currency from Currency dim.

• This works correctly on tile and table visualizations only when I edit the properties of each visual and set the Aggregation Method to Sum and the By to Ledger Currency.
• However, it doesn't work if these properties are not set on the visual.
• Instead, it generates a list of the total sum multiplied by each rate, and for the total shows the first value in the list.
• If I wrap a sum around the formula, I get a sum of these three values, which is incorrect.

Is there a formula method to get the same results as setting Aggregation Method to Sum and BY to Ledger Currency on the visual?

Fred Kaffenberger-Oracle

I recreated the issue using two file uploads joined.

 59,986 is correct sum of each resource multiplied by respective rate

59,010 is 420* 140.5 ( sum multipied by first rate in the list 140.5)

TO get correct total on a tile, must always use set Sum By on Values under Properties.

If visual property not set to sum by on values tab, then formula using Sum By can only show a correct formula if an attribute is also displayed on the visual (see left set of visuals).

Without Attributes displayed on a visual, visual will only show the first in a series of values. In Formulas without Attributes table on the left side, the values are total hours * each distinct rate. In this table, the Sum By formula is correct for each value, but for the total displays the first result only. 

Sum By function & Visual setting.dva

Resource	Hours	Rate	Row Total
AMK	2	140.5	281
AMK	5	140.5	703
AMK	15	140.5	2,108
AMK	40	140.5	5,620
ARG	35	150	5,250
ARG	40	150	6,000
ARG	40	150	6,000
ARG	50	150	7,500
EKK	25	125	3,125
EKK	35	125	4,375
EKK	40	125	5,000
EKK	45	125	5,625
FRK	3	175	525
FRK	5	175	875
FRK	15	175	2,625
FRK	25	175	4,375
TOTAL	420		59,986