Formula to to always aggregate by a dimension and allow other grouping in visual

Fred Kaffenberger-Oracle

We have a formula that should always be aggregated by the Ledger Currency in FDI but it also may need to be visualized by other dimensions: Fiscal Year, or Business Unit, Supplier, etc.

Currently, we set the values tab on the properties tab for the visual

• Aggregation Method: Sum
  
• By: Ledger Currency, Fiscal Year, etc.

Attempt using formula:

Using aggregate by function, we can get enforce the granularity of the calculation, but this overrides the default aggregation of the visual.

sum( aggregate( Payments Amount * Ledger Exchange Rate by Ledger Exchange Rate )

The formula above will show the right total for a card visual, but if put on a bar chart with fiscal year on the axis, it shows the same amount for every fiscal year.

Is there any way to write a formula that will work well in a variety of contexts, or is this something that must be set every time the calculation is used on a visual?

Thanks,

Chere-Oracle

If you are using the BI Administration Tool for your data modeling, you can create multiple logical table sources within one logical fact table. Whichever logical table source fits the granularity of the analysis will be used for running the analysis. For example, if one table source includes a mapping to both Ledger Currency and Fiscal Year, while another includes a mapping to Ledger Currency (but not to Fiscal Year), the table source that contains Fiscal Year will be used only if Fiscal Year is included in the analysis; otherwise, the other table source will be used.

This means a fact column can have a different aggregation formula for each level of granularity.

NOTE: I do not know if this works for high-granularity visuals that are built off the results of lower granularity analyses. It may be that the granularity of the underlying analysis must match the visual's granularity.

Moreover, I've never tried this strategy in DV.