Best way to handle multiple currencies

Hi James,

The best way to prevent users summing over multiple currencies is to force a GROUP BY clause in your measure to ensure separation in your totals.

To achieve this Looker has a required fields parameter.

The required_fields parameter allows you to pull additional fields into a query when a user chooses a specific field, in your case currency.

This forces the grouping clause.

Your measure will look like this:

 measure: totalrevenue {


        type: sum


        sql: ${sale_price} ;;


        required_fields: [currency]


      }

This will form a SQL command that looks like this:

SELECT 


	order_items.currency AS `order_items.currency`,


	COALESCE(SUM(order_items.sale_price ), 0) AS `order_items.totalrevenue`


FROM demo_db.order_items  AS order_items





GROUP BY 1

Its is important to note however that although the required fields are added to the query, they are not displayed to the user. This can be confusing but can be overcome by adding a description in the measure that will be displayed to the user in the field picker under the i symbol.