Forum Discussion
Groub by ID
Hello dear Power BI Community
Following problem to solve:
Let's assume this table:
| ID | Sales |
| A | 1 |
| B | 1 |
| C | 2 |
| D | 2 |
| E | 2 |
| F | 4 |
| G | 5 |
The following result i would like to habe:
| Num of ID | Sales |
| 2 | 1 |
| 3 | 2 |
| 1 | 4 |
| 1 | 5 |
The table tells, that:
2 ID's have a sale of 1 ( A&B)
3 ID's have a sale of 2 (C&D&E)
1 ID has a sale of 4 (F)
1 ID has a sale of 5 (G)
...
Any ideas how to solve this?
Thank you very much for your contribution
Cheers
qwertzuiop
qwertzuiop this is an interesting query and backwards to the default for Power BI.
You're wanting to use the numeric field / column [Sales] as the category / filter / dimension. So you're actually wanting to group by Sales Amount.
In a table visual, add Sales and ID.
Right click on Sales in the visualizations pane and set to 'Don't Summarize'
Right click on ID and set to 'Count'
2 Replies
qwertzuiop this is an interesting query and backwards to the default for Power BI.
You're wanting to use the numeric field / column [Sales] as the category / filter / dimension. So you're actually wanting to group by Sales Amount.
In a table visual, add Sales and ID.
Right click on Sales in the visualizations pane and set to 'Don't Summarize'
Right click on ID and set to 'Count'
Create a Measure:
NumofIDs = DISTINCTCOUNT( Sales[ID] )Then drag this into your Table or other visual along with Sales
Regards,
