I have two My SQL Data sources: Users and Orders
dimension of Users:
- user ID
- created time
dimension of Orders:
- user ID
- order ID
- price
I combined two Data sources with the left join through user ID
Then created a table with columns:
- user ID
- created time
And added two metrics:
- number of orders (through the formula COUNT (price)
- the sum of the price of all orders (through the formula SUM (price)
Formulas display the sum and number of only unique values, but the sums for one user are sometimes repeated, so the data turned out to be incorrect
How can I calculate the sum and number of all user's orders so that all values (unique and non-unique) are taken into account
or maybe there is another way to display a table with columns:
- user ID
- created time
- number of orders
- the sum of the price of all orders
P.s. When trying to add order ID to dimension, it always displays null