Google Data Studio : how to obtain a SUM related to a COUNT_DISTINCT?

Question

I have a dataset including 3 columns :

• ID transac (The unique ID of the transaction - Dimension)
• Source (The source of the transaction - Dimension)
• Amount € (The amount of the transaction - Stat)

screenshot of my dataset

• To Count the number of transactions (for one or more sources), i use COUNT_DISTINCT function

• I want to make the sum of the transactions amounts (for one or more sources). But i don't want to additionate the amounts of the transactions with the same ID !

Is there a way to do this calcul with a DataStudio function ?

Thanks for your answers. :-)

EDIT : I saw that we could do this type of calculation via SQL here and I would like to do this in DataStudio (so that I don't have to pre-calculate the amounts per source.)

Answer 1

IMO, your dataset contains wrong data. Each value should be relative only to that line, but this is not the case: if the total is =20, each line should describe the participation of that line to the total. With 4 sources, each line should be =5 or something else that sums 20.

To solve it in DataStudio, you need something like CALCULATE function in PowerBI, but currently DataStudio doesn't support this feature.

But there are some options to consider to repair your data:

• If you're sure there are always 4 sources, just create a new calculated field with the expression Amount/4 and SUM it. It is not an elegant solution, but it works.

• If your data source is Google Sheets, you can easily repair the data using formulas, like in this example:

  

  Link to spreadsheet

  For this spreadsheet, I used this formula in adjusted_amount column: =C2/COUNTIF(A:A,A2). With this column in DataStudio, just use the usual SUM aggregation function to summarize it correctly.