Forum Discussion
Newbie needing simple? table calculation help
Hi,
Go to Home > Edit Queries and click on the Table named Data to see the transformation steps i applied. Here are the answers to your specific questions:
- Using Data > Split column > Advanced > By rows
- Done in the Query Editor. Once click solution to create an Index column
- Done in the Query Editor.
If my previous reply helped, please mark as Answer.
Hi - thanks for the reply, but I don't think this is the solution I was looking for. For your table, the answer I'd be looking for would be the count of all the occurrences of "One" in the column, the count of all the occurences of "Two" in the column, and the count of all the occurences of "Three" in the column. Example;
Column 1 Column 2 Count of Occurences of Column 1 in Column 2
One One 3
Two One, Two 2
Three One, Two, Three 1
If you do understand, I have a sligthly more complex example in the files. If you could put the solution in the Power BI file or use the following data set. I REALLY appreciate the help.
Type Primary Back-up
| Supplier | Robert Smith | James Johnson;Maria Garcia |
| Customer | Maria Garcia | Mary Smith;Bob Williams;James Johnson |
| Internal | Mary Smith | Robert Smith |
| Internal | James Johnson | Bob Williams;Robert Smith;Mary Smith |
| Customer | Bob Williams | Robert Smith;Maria Garcia |
| Supplier | Robert Smith | James Johnson |
| Supplier | Robert Smith | |
| Supplier | Robert Smith | |
| Internal | Mary Smith | Maria Garcia |
| Customer | Maria Garcia | Bob Williams;James Johnson |
| Internal | Bob Williams | James Johnson;Mary Smith |
| Customer | Robert Smith | Bob Williams |
The answer I'm looking for is
| Customer | Internal | Supplier | ||||||||||
| Primary | Count of Primary | Count of Back-up | Total Customer | Count of Primary | Count of Back-up | Total Internal | Count of Primary | Count of Back-up | Total Supplier | Total Count of Primary | Total Count of Back-up | Grand Total |
| Bob Williams | 1 | 3 | 4 | 1 | 1 | 2 | 0 | 0 | 0 | 2 | 4 | 6 |
| James Johnson | 0 | 2 | 2 | 1 | 1 | 2 | 0 | 2 | 2 | 1 | 5 | 6 |
| Maria Garcia | 2 | 1 | 3 | 0 | 1 | 1 | 0 | 1 | 1 | 2 | 3 | 5 |
| Mary Smith | 0 | 1 | 1 | 2 | 2 | 4 | 0 | 0 | 0 | 2 | 3 | 5 |
| Robert Smith | 1 | 1 | 2 | 0 | 2 | 2 | 4 | 0 | 4 | 5 | 3 | 8 |
| Total | 4 | 8 | 12 | 4 | 7 | 11 | 4 | 3 | 7 | 12 | 18 | 30 |
Super UserLooks like it works Ashish, thanks! I have some questions so I can repeat the solution.
- How did you split the backup out?
- How do I create the column "Index"? Is is a basic function that Power BI will do in creating the split?
- How do I create the column "Back-up combination", it looks like a concatination of the index and the split backup column but it is not a calculated measure. How do you do this?
I'm sorry for the newbie questions. Thanks for your valuable time.
Also - is the table pirmary supplier names needed? It is blank as far as I can see.
- Ashish_Mathur
Hi,
Go to Home > Edit Queries and click on the Table named Data to see the transformation steps i applied. Here are the answers to your specific questions:
- Using Data > Split column > Advanced > By rows
- Done in the Query Editor. Once click solution to create an Index column
- Done in the Query Editor.
If my previous reply helped, please mark as Answer.
Ashish - thanks for the quick response and sorry for my delay getting back to you. You did it! I wanted to verfiy that I could re-create the solution on my side but got caught up in some other work.
- Ashish_Mathur
You are welcome.