Calculating a ratio based on partial text values

[a3810]

Sorry if this question seems too simple, but I am looking for a way to insert a quick measure which calculates the ratio of certain values.

Worker	Service	Cost
James	Initial service	$1
Harry	Initial service	$2
Steve	Review	$2
Harry	Review Comp	$0
Steve	Initial Appointment	$3
James	Review	$2
Steve	Initial Consult	$5
James	Review	$2
James	Review > 7	$3
Steve	Initial Comp	$4
Steve	Initial	$3

I want to count all services that include the term 'Initial' and divide by the number of total Services for a worker, ie calculate the ratio of services that are 'Initial' compared with the workers total services. 

I can filter to count the number containing 'Initial' but not then link to the total number for a worker. Can anyone assist? 

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[a3810]

This is really helpful.

When i run the Is the Service Initial measure, I get the error:

A single value for column 'Item' in table 'Master Activity Report' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result. 

Could it be because my service value descriptions are long. Here is an example:

[Ashish_Mathur]

I do not know the reason.  Review my solution carefully and retry.

[a3810]

Sorry, you are correct. It was an error at my end. 

However, when I look at my data, the number is Is my service initial? calculation is less then if I run a filter manually, selecting only those services which have the term initial. This is not doing a distinct count is it?

Really appreciate your help. 

[Ashish_Mathur]

I do not understand your question.  Based on the Table that you shared in the first post, show the expected result.

[a3810]

Sorry if I was unclear. 

The table on the far left is from manually filtering the data to select only items with the term initial used. 

The middle table is the total number of items per person. 

The one on the right is using your code to create a new measure. Given the difference, could your code be counting only distinct codes which contain the word 'initial'?

I would just use the filtered number, but I need Power Bi to do the calculation automatically, hence the need for a new measure. 

Sorry to keep coming back to you. I know this is proabaly a really easy issue that I am just not understanding. 

[Ashish_Mathur · Accepted solution]

Share the download link of the PBI file with the 3 visuals already built there.

[a3810]

Thanks so much Ashish! Your solution has helped me so much!!

Really appreciate your time and expertise!

[Ashish_Mathur]

You are welcome.

[amitchandak]

@a3810 , based on what I got

Divide(

Calculate( Sum(Table[Cost]), filter(Table, containsstring(Table[Service],"Initial"))) , Sum(Table[Cost]) )

or

Divide(

Calculate( Count(Table[Service]), filter(Table, containsstring(Table[Service],"Initial"))) , Count(Table[Service]) )

[a3810]

And when I try:

Measure 2 = Divide(

Calculate( Sum('Master Activity Report'[Item]), filter('Master Activity Report', containsstring('Master Activity Report'[Item],"Initial"))) , Sum('Master Activity Report'[Item]) )

It gives a red error over "Initial"

The word initial is part of the code description. Should it start with an * as it is a partial match?

Thanks again. 

[a3810]

Thanks Amit.

I have used the second option, however I get the error saying that 'To use special characters in a measure, enclose the entire name in brackets ( [] ) and add a ] to any closing brackets in the name. 

Is the issue in my table or sheet names? Here is my input.

Divide(

Calculate( Count(Master Activity Report[Item]), filter(Master Activity Report, containsstring(Master Activity Report[Item],"Initial"))) , CountMaster Activity Report[Item]) )

Thanks so much for your response.