cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
alicia57 Regular Visitor
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

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.

alicia57 Regular Visitor
Regular Visitor

Re: Sum of values in a column

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

Moderator v-qiuyu-msft
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:

 

a3.PNGa2.PNG

 

 

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.
alicia57 Regular Visitor
Regular Visitor

Re: Sum of values in a column

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

Moderator v-qiuyu-msft
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.

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

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!

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?

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.

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors