Forum Discussion
Newbie needing simple? table calculation help
Hi - I'm hoping someone can help me. I'm very new, literally hours in, and a recent convert to Power BI from Tableau. I just couldn't take the special way you needed to do and think about things differently using Tableau. Unfortunately, I did have some dashboards already built that I need to re-create in Power BI. Anyway...
I need to count the occurrence of a name in a semicolon separated column of data. I need the count to be in a table with the count of another name field. I tried some simple functions and can't seem to get the calculation to work. In the example data attached, I need a count of the occurrences of the Primary & Back-up name columns. A simple count only counts the occurrences where the entire combined data exists. Hard to explain but looking at the data should help. I also don't know how to do simple stuff yet like add grand totals and subtotals to the columns. I'm good at Excel pivot tables, and I'm guessing it is similar in a Matrix - - just haven't figured out where everything is yet.
I have a spreadsheet with example data in a table, and the answer I'd like to obtain from in a Matrix. I also have the data in Power BI Matrix desktop file. I'd appreciate if someone could help me out sooner than later if possible. The link will take you to the files. https://drive.google.com/open?id=1dYXi75tZ4TXxiBuMjfLmkPTP8c7WRNbI
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.
8 Replies
I 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
