Forum Discussion
Calculating a ratio based on partial text 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 |
Share the download link of the PBI file with the 3 visuals already built there.
12 Replies
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]) )
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.
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.
Hi,
PBI file attached.
Hope this helps.
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:
I do not know the reason. Review my solution carefully and retry.
