comparisons between entire column of data and a keyword picked from the same column

[AndyTilley]

Hi there

I have been a PBI user for a few months now, I am ok with most functions and have done quite a few tutorials and self help but this one has me stumped.

I have a CSV file with a column of data called ActionCodeComments (details of the work carried out go here)

Within that column of data I want to search for a keyword "divert"

That is fairly easy with a filter

But what I want to do is display the count of the entire column lets say its 20,000 jobs.

also display the count of the jobs within that column with "divert" in them lets say its 356

Then display the percentage of that 356 against the 20,000 to see what percentage of call outs we have had against the entire amount of jobs.

Hope that explains it ok.

I have looked at a measure and found ways to do things, but not found a way to pull a keyword from the same data and compare it against....the same data!

Am I missing something.

This isnt my day job, analytics, I am a regional service manager, so I am still learning as I go and really excited about PBI and am also trying to convice the exec's to adopt the platform and stop using excel (I cant watch another 50 tabs of pivot table data anymore its heartbreaking!)

[CheenuSing]

Hi @AndyTilley ,

Can you upload sampledata, pbix and the output expected to GoogleDrive/OneDrive and share the link here to formulate a solution.

Cheers

CheenuSing

[AndyTilley]

Hi Cheenu

Thanks for the reply

I have placed the sample data csv file and highlighted the lines in it with conditional formatting .

In that column of 51 rows, 13 of them have the keyword "divert"

What I cannot seem to figure out with any visualisation is how to split the key word data away from the entire data, and show the percentage difference, with ease.

Every visual way I have tried doesnt work, as I can only filter one keyword and get one output

its either 13 jobs or its 51 jobs, never the comparison of the two nor can I figure out what is happening to percentages (which is 25% in this case) and display it.

I dont have a PBI file to offer as through my frustrations I have all but given up that I can do this 

Basically my expected output would be a visual like a 100% stacked column withthe total of 51 jobs, a stack there of 13 jobs showing the percentage overall.

Hope that makes sense and thanks for any help

Sample data file

[HotChilli · Accepted solution]

Add a column in Power Query like this

and 'Close and Apply'.

In powerbi, create a measure,

Count HasDIvert = COUNT(yourTable[HasDivert])

Then in Report View, create a table and pull HasDivert on to it.

Then pull the measure you created on to the table twice.

In the field well, click the down arrow on one of the measures and select 'Show value as -> percent of grand total'

You'll then have the following (showing a stacked column chart too)

[AndyTilley]

BRILLIANT!

Its exactly what I wanted.

Thank you very much

I am off now to investigate all of the code you put up and find out what it does, all good learning