Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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.
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
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
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |