cancel
Showing results for
Did you mean:
Regular Visitor

## Sum of values in a column

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

5 REPLIES 5
Member

## Re: Sum of values in a column

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.

Regular Visitor

## Re: Sum of values in a column

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

Moderator

## Re: Sum of values in a column

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

## Re: Sum of values in a column

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

Moderator

## Re: Sum of values in a column

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.