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.
Community ChampionI created a quick measure for things like this, you can see it here:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Count-of-List-Items/m-p/350084
Welcome!!
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 - Ashish_Mathur
Super User
Looks 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.