Sum of values in a column

[alicia57]

Hello,

I have a problem using the function distinctcount.

For example, I display the following matrix:

          New Old

2014  150  200

2015  220  170

2016  180  190

The first column is the year, the second the number of new clients and finally the number of old clients.

To get these number I used the function distinctcount in order to have the distinct number of clients by year, and not the total number of transactions.

But if I display the automatically calculated total, I have the distinct number of clients for these three years, whereas I would like to have:

New Old

550  560

Would you have an idea how to get this result? 

Thank you very much,

Alicia

[v-qiuyu-msft]

Hi @alicia57,

From your description, it seems that the New and Old are from the same column field, right?

To calculate total of distinct count of New and Old client numbers, you can directly set the matrix values use Count(distinct) aggregation. See:

Best Regards,
Qiuyun Yu

[alicia57]

Hi @v-qiuyu-msft,

Thank you for your answer! 

Indeed, that's what I did. The problem is the total.

In your example, for New, the total is 7, that's to say the number of unique client. But I would like 9 for New and 10 for Old in your example.

Regards,

Alicia

[v-qiuyu-msft]

Hi @alicia57,

As you used Count(distinct) aggregation, the total should discount New type both in the year 2014, 2015 and 2016. You can see from my sample data, both 2014 and 2016 year have the amount 2 for the New type. The total distinct count value shouldn't be the total of each year's distinct values simply, the total 7 for the New type is correct, as some value maybe duplicate in those three years which need to be treat as one.

Best Regards,
Qiuyun Yu

[LaurentCouartou]

You need to use the SUMX function to meet your requirements.

However, remember you will have the same "discrepancy", if you report at a daily level. If you count unique customers at the daily level, results will not add up at the monthly level.

[alicia57]

Thank you for your answer!

To specify, my table is displaying as a matrix but no column contain these data by year.

In this case, is it possible to use the function SUMX()?

Thank you,

Alicia