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

 

---

Wow you are good:) This is ALMOST what I wanted, I have tried to replicate your answer above, and I noticed two things.

Lets say there is 10 rows total for name 1, Amount has been changed a total of 4 times, then it currently says 0.40 if I aint format it, if I chose "show value as percent of row total" it says 57.14%, if I chose "show value as percent of column total" it says 100% , if I chose "show value as percent of grand total" it says 57.14% , however what I want is it to say 60%, because 60 of these 10 rows for this name wasnt changed, so 60%  of the rows was correct, but I aint sure how I can achieve this.

I also noticed you use

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

And some of my columns in my real data does in fact have a space in the name sadly

So I changed your line into: var ConvertColonToSpace = SUBSTITUTE('Transfer (TableC)'[Message],":",":")

and it works now, however it says Amount: Kred: with a : in the name etc etc, is there anyways to avoid this or?

The last thing I noticed is the "Total column", is there any way I can get this total column to show the positiv % of the row (for each name)?

And how come there is blank values, for example in your screenshot it says Name1-Kred 100% but Name1 RestAmount is blank? 

If nothing has changed at all it should be 100%:P

Best regards

And once again, the help is really appreciated :) 

---

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.

---

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

 

---

Also in need of a column that counts how many rows there if for each "Name" in table B if possible:D