How to count number of cells as a new column?

[ianwuk]

Hello All.

I have this data:

Date           Order ID       Product

20/7/17      12345            Shoes

20/7/17       2222             Boots

I want to add a new column that can act as way to count the number of orders for the month that I can then total.

So I want it to look like this:

Date     Order ID   Product   Count

20/7/17  12345     Shoes        1

20/7/17   2222      Boots        1

Therefore I can then total that and get a total number of orders for the month, does that make sense?

Thanks.

Ian

[Anonymous]

Do you want this column to have 1 on every row?  If so just create a column with the formula

COUNT = 1

Also there is a dax formula called COUNTROWS which might do what you wanted to do, without the need for the extra column

[ianwuk]

Hello @Anonymous, thank you for replying.

I made a new column with count=1 and some of the cells were not 1 (e.g. they were 3 or 5 and some were blank) when this was summed to give a total.

I then tried a new column with just 1 in it (= 1) and I got the same when it was summed.  If I do not sum then it displays 1 in each cell, which I want, but I also want a total of those 1's as well.

Hope this makes sense.

[Anonymous · Accepted solution]

Hi @ianwuk

a smoother way to go about this is to count the unique orders you have in your table, since this is exactly what you are trying to achieve.

Create it as a measure:

Distinct count of orders = DISTINCTCOUNT(Table[Order ID])

This will give you a total of all the unique orders. You can also plot it in a table according to your need. Hope this answers your question, if you want me to elaborate, let me know.

Best,

Martin

[ianwuk]

Thank you for replying @Anonymous.

I tried that and it did not help me.  I will try and explain in more detail.

Here is what my data looks like as a visualisation (using table style).

License Key        Number of Activations

12345                  1

11111                  1

22222                  3

33333                  3

44444                  2

55555                  1

66666                  1

If I sum that as a total it should come up as 12. 

When I choose to sum the number of activations shown above Power Bi changes the data to this.

License Key        Number of Activations
12345                  1
11111                  6
22222                  6
33333                  6
44444                  215
55555                  215
66666                  215

And gives me a total of 222.

This is clearly wrong.  How can I get it to count correctly in my displayed visualisation?

Or, how can I simply add a new ID column that acts as counter?  So it would look like this:

ID           License Key        Number of Activations
  1                12345                  1
  2                11111                  1
  3                 22222                  3
  4                 33333                  3
  5                   44444                  2
  6                   55555                  1

At least with the above I could see at a glance that a total of six license keys were activated.

I hope this makes sense.

Many thanks.

Ian 

[ianwuk]

Hello Everyone.

A quick update, I think I have solved it, thanks to @Anonymous.  I set up a new column in query editor and used that to count the respective orders and get the correct total.

Thanks everyone for your help!