Question regarding measure

---

Phil_Seamark wrote:

Hi Shamatix

 

I got your sample model and here is what I've got so far.

 

I added a calcuated column to your TableC using the following code :

 

Column Name = 
var ConvertColonToSpace = SUBSTITUTE('Transfer (TableC)'[Message],":"," ")
var FirstSpace = FIND(" ",ConvertColonToSpace,1,0)
return LEFT(ConvertColonToSpace,FirstSpace)

This gave me a field that I could drag to the Column of the matrix visual

 

I then created the following calculated measure

 

Count of rows in Table B = CALCULATE(
				COUNTROWS('Data (TableB)') , 
				ALLEXCEPT('Cred (TableA)','Cred (TableA)'[Name])
				)

Which will give me the overall total of rows in TableB for each Name - which I will use in my final calculated measure :

 

Measure = DIVIDE(
		COUNTROWS('Transfer (TableC)'),
		'Data (TableB)'[Count of rows in Table B]
		)

If you create that measure (use a better name) on the model you uploaded, and format as percentage, I got the following result

 

---

For whatever reason my reply has been removed, so now Ill have to make it again.

I really like the solution above however I have 3-4 questions to achieve the exact result I want.

1. Lets say there exsist 10 rows in TableB with the value "Name1", 4 of the rows has changed amount which can be seen in TableC, now I get the result 0,40, if I put on "Show value as percent of grand total" it says 57.14%, if I choose "Show value as percentof row total" it says 57.14% and if I choose "Show value as percent of column total" it says 100%, but the result I want is 60%, because 4 out of 10 time the Amount has changed for Name1, therefor the value has been correct 60% of the time, so I want it to return 60%.

2. How come there is blank values? 100% should mean that the value has NEVER been changed for this "Name" and 0% should mean it has been changed ALL the times.

3. I noticed your line:
var ConvertColonToSpace = SUBSTITUTE('Transfer (TableC)'[Message],":"," ")

var FirstSpace = FIND(" ",ConvertColonToSpace,1,0)
And I use space in some of my column names in the real data, so what I did was change it to the following:

var ConvertColonToSpace = SUBSTITUTE('Transfer (TableC)'[Message],":",":")

var FirstSpace = FIND(":",ConvertColonToSpace,1,0)

Now it results in Name: Amount: Rest Amount: Inv Date:  etc etc etc, is there anyways to get rid of the : in the column names but still get the space?

Once again I really appreicate the above you have made^^ And deffi getting closer to the end result, but perhaps you can help me with the above as well:)

best regards.