Question regarding measure

[Shamatix]

 Hey fellow users of PowerBI,

I have a question regarding a measure and something I want to achieve.

I have 3 tables

Table A

Column (Unique): Name

Table B (Main table)

Ton of data 

Table C (History table)

Has a message column saying: ColumnXTableB has changed from '' to 'abc' 

ColumnBTableB has changed from value 'asd' to 'sad'

All the 3 tables are linked together, now I want to create a table where first row is using Name from table A which is unique. 
Table B has a column with names from Table A for each row. I want to create a table which has most of the rows from Table B which has a percentage in each row / column for each name in table based on how often something has changed in table C to how many rows there are total.

Forexample if we take a look at the below picture and look at "Henry", lets say there is a total of 10 lines having the value (name) Henry in Table B, then I can see in Table C in column "Message" that "ValueBTableA has changed from 'abc' to '123'

So it has changed 1 out of 10 times (10%) so 90% of the time the value was correct and therefor wasnt changed,

one more example could be Henry Value A, here Message in Table C would say "that ValueATableA has changed from '' to '1253' and that would have happened 6 times, (60%) so only 40 of the time the value wasnt changed and therefor correct)

I hope you understand what I want to achieve, but I aint sure how to.

Hopefully some of you guys can help me out:) 

[Phil_Seamark]

HI @Shamatix

Any chance you can post some sample data?  Your explanation is good but it might help to provide a sample of data to work with.

[Shamatix]

---

@Phil_Seamark wrote:

HI @Shamatix

Any chance you can post some sample data?  Your explanation is good but it might help to provide a sample of data to work with.

---

Hey,

I have fast made a power bi report here https://uploadfiles.io/n80cv  (Unsure where I could upload it)

Anyways there is the 3 tables and a fast relationship table i need, well I have way more data but this is kind of the structure I have and I hope you get the larger idea of it all once you see those tables.

Feel free to ask me any questions needed be and Ill do my best to answer, you can also feel free to add me on skype: Snaske2 if it might be easier for you.

[Phil_Seamark]

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

[Shamatix]

---

@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

[v-ljerr-msft]

Hi @Shamatix,

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. 

---

---

---

Try using the formula below to create the measure.

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

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?

Try using the formula below to create the column.

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

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

You should be able to use the formula below to create a calculate column in table b to counts how many rows there for each "Name".

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

Here is the modified pbix for your reference. 

Regards

[Shamatix]

---

@v-ljerr-msft wrote:

Hi @Shamatix,

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. 

---

---

---

Try using the formula below to create the measure.

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

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?

Try using the formula below to create the column.

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

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

You should be able to use the formula below to create a calculate column in table b to counts how many rows there for each "Name".

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

Here is the modified pbix for your reference. 

Regards

---

Hey, 

Thanks a ton for the answer, however I still encounter two problems, and they go as follows:

On the below picture you can see I have dragged in "Count of names in Table B" in the columns but there come no new column?

I basicly want a column to the furthest right, telling me how many rows exsist for NameA in Data(B), how many rows exsists for Name2 etc etc

Also I am still unsure how you got everything to be in Percentage, where do you do this? 

[Shamatix]

Nevermind I figured out the percentage, but I still havnt figured out the count pr names from data table, how to get it in.

[v-ljerr-msft]

Hi @Shamatix,

Try clicking the (expand all down one level in the hierarchy) on the left top of the table. 

Regards

[Shamatix]

Ye I tried that, but aint really what I want,

I want the following 

Where the new columns number just says "Okay there is 10 rows for Name1 in tableB, 0 rows for Name 2 in Table b, 0 rows for Name3 in table B" etc etc 

[v-ljerr-msft]

Hi @Shamatix,

---

@Shamatix wrote:

Ye I tried that, but aint really what I want,

I want the following 

Where the new columns number just says "Okay there is 10 rows for Name1 in tableB, 0 rows for Name 2 in Table b, 0 rows for Name3 in table B" etc etc 

---

As far as I know, there isn't an easy way to do this currently. 

Regards

[Shamatix]

Based on this post I now have the following matrix:

What is the total to the right? how can something be -400% it should just show the average of the row? but what is this showing, and since those values are wrong, can I either disable it or get it to show the average of the row?

[v-ljerr-msft]

Hi @Shamatix,

You can disable it by turning off Row subtotals and Column subtotals in the Format tab for the Matrix. 

Regards

[Shamatix]

---

@v-ljerr-msft wrote:

Hi @Shamatix,

You can disable it by turning off Row subtotals and Column subtotals in the Format tab for the Matrix. 

Regards

---

What is it exactly the value is showing tho? As its not the average of the row?

[Shamatix]

---

@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.

[Shamatix]

Sadly I still havnt managed to get the result I want=/

[Shamatix]

bump

[Shamatix]

---

@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 🙂