Compare Rows

[ckausihan_12]

Hi, I have a table (Table) which contains student data, with grades they achievd in each academic term. I have created a matrix (Matrix) which uses the data from Table and measures which identifies progress made from baseline to selected term.  I need to find a way to count the number of students who have made positive progress from baseline to select term.

So person1 has made progress from baseline to aut by +1, which means person1 has made positive progress and therefore would count towards the number of students who made progress from baseline to aut. However, from baseline to spr person1 made -1 progress, meaning person1 wont count towards the total for baseline to spr. I will have three totals: base to aut, base to spr and base to sum.

Table:

ID	Name	Term	Data_Drop_Order	Baseline	Aut	Spr	Sum
1234	person1	Sum	4	0	0	0	5
1234	person1	Spr	3	0	0	3	0
1234	person1	Aut	2	0	5	0	0
1235	person1	Base	1	4	0	0	0

Matrix:

Name	Baseline	Aut	Spr	Sum	ProgBaseAut	ProgBaseSpr	ProgBaseSum
person1	4	5	3	5	1	-1	1

[v-zhenbw-msft · Accepted solution]

Hi @ckausihan_12 ,

We can use the following steps to meet your requirement.

1. Create three measures to calculate the difference.

ProgBaseAut 2 = CALCULATE(SUM('Table (2)'[Aut]),ALLEXCEPT('Table (2)','Table (2)'[Name])) - CALCULATE(SUM('Table (2)'[Baseline]),ALLEXCEPT('Table (2)','Table (2)'[Name]))

ProgBaseSpr 2 = CALCULATE(SUM('Table (2)'[Spr]),ALLEXCEPT('Table (2)','Table (2)'[Name])) - CALCULATE(SUM('Table (2)'[Baseline]),ALLEXCEPT('Table (2)','Table (2)'[Name]))

ProgBaseSum 2 = CALCULATE(SUM('Table (2)'[Sum]),ALLEXCEPT('Table (2)','Table (2)'[Name])) - CALCULATE(SUM('Table (2)'[Baseline]),ALLEXCEPT('Table (2)','Table (2)'[Name]))

2. Then we can create three measures to determine if the first three measures are greater than 0.

BAut 2 = IF([ProgBaseAut 2]>0,1,0)

BSpr 2 = IF([ProgBaseSpr 2]>0,1,0)

BSum 2 = IF([ProgBaseSum 2]>0,1,0)

3. At last we can create a measure to calculate the count of person who meet the conditions.

count Person 2 = CALCULATE(DISTINCTCOUNT('Table (2)'[Name]),FILTER('Table (2)',[BAut 2]=1 && [BSpr 2]=1 && [BSum 2]=1))

The result like this,

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-zhenbw-msft]

Hi @ckausihan_12 ,

We can use the following steps to meet your requirement.

1. Replace [BAut], [BSpr] and [BSum] with the following three measures.

BAut = [ProgBaseAut]>0

BSpr = [ProgBaseSpr]>0

BSum = [ProgBaseSum]>0

2. Then we can create a measure to calculate the count of person who satisfies condition.

count Person = CALCULATE(DISTINCTCOUNT('Table'[Name]),FILTER('Table',[BAut] && [BSpr] && [BSum]))

The result like this,

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ckausihan_12]

Hi @v-zhenbw-msft ,

Unfortuently this soltiuon won't work for me, since each person has an indivdual entry for every term. My dataset layout is as follows:

[ckausihan_12]

Hi @v-zhenbw-msft ,

I made some changes to clarify any confusion.

I have added an extra person into my example. Please see the matrix below.

I added three measures to calculate how much progress was made from baseline to the terms. I need these, as they will help me determine who made positive progress, stayed the same or made negative progress (I am only interested in positve progress).

ProgBaseAut = SUM('Table'[Aut]) - SUM('Table'[Baseline])

ProgBaseSpr = SUM('Table'[Spr]) - SUM('Table'[Baseline])

ProgBaseSum = SUM('Table'[Sum]) - SUM('Table'[Baseline])

I added three additional measures to help identify the people who made positive progress.

BAut = IF([ProgBaseAut]>0,1)

BSpr = IF([ProgBaseSpr]>0,1)

BSum = IF([ProgBaseSum]>0,1)

I essentially just need a count of the people who have made positive progress, so count of BAut, BSpr and Bsum when equals 1.

Please see the example table below I with my desited results:

Term	No. Positive Progress
Aut	1
Spr	1
Sum	2

[v-zhenbw-msft]

Hello @ckausihan_12 ,

We have some confused ones.

In the matrix table, the baseline of person1 is 4, Aut is 5, Spr is 3. But in table person4 the baseline is 4, Aut of person3 is 5. And we can't imagine what your resulting wish is.

So we guess a solution and you can refer.

1. Create three measures to calculate the [ProgBaseAut], [ProgBaseSpr], [ProgBaseSum].

ProgBaseAut = CALCULATE(COUNT('Table'[Name]),FILTER('Table','Table'[Aut]>'Table'[Baseline]))

ProgBaseSpr = CALCULATE(COUNT('Table'[Name]),FILTER('Table','Table'[Spr]>'Table'[Baseline]))

ProgBaseSum = CALCULATE(COUNT('Table'[Name]),FILTER('Table','Table'[Sum]>'Table'[Baseline]))

2. Then we can create a new table using Enter Date, and create a measure to summarize the three measures.

Total = 
SUMX(
    VALUES('Table (2)'[Row]),
SWITCH(
    'Table (2)'[Row],
    "ProgBaseAut",[ProgBaseAut],
    "ProgBaseSpr",[ProgBaseSpr],
"ProgBaseSum",[ProgBaseSum]))

If you do not meet your requirement, could you show the expected exact result based on the table you shared?

BTW, pbix as attached.

Best regards

Community Support Team _ zhenbw

If this post helps, please consider accepting it as the solution to help other members find it more quickly.

[v-zhenbw-msft]

Hi @ckausihan_12 ,

We have some confused.

In your matrix table, the person1’s base line is 4, Aut is 5, Spr is 3. But in table person4’s base line is 4, person3’s Aut is 5. And we don’t imagine what is you desire result.

So we guess a solution and you can refer.

1. Create three measures to calculate the [ProgBaseAut], [ProgBaseSpr], [ProgBaseSum].

ProgBaseAut = CALCULATE(COUNT('Table'[Name]),FILTER('Table','Table'[Aut]>'Table'[Baseline]))

ProgBaseSpr = CALCULATE(COUNT('Table'[Name]),FILTER('Table','Table'[Spr]>'Table'[Baseline]))

ProgBaseSum = CALCULATE(COUNT('Table'[Name]),FILTER('Table','Table'[Sum]>'Table'[Baseline]))

2. Then we can create a new table using Enter Date, and create a measure to summarize the three measures.

Total = 
SUMX(
    VALUES('Table (2)'[Row]),
SWITCH(
    'Table (2)'[Row],
    "ProgBaseAut",[ProgBaseAut],
    "ProgBaseSpr",[ProgBaseSpr],
"ProgBaseSum",[ProgBaseSum]))

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ckausihan_12]

Hi @v-zhenbw-msft ,

I do apolgise for the confusion! I just spotted the error I made. The table should just contain data for person1. So it shows one persons grades for baseline, aut, spr and sum. 

[Greg_Deckler]

@ckausihan_12 Sorry, not following this, why exactly has Person 1 made progress from baseline to aut by +1?

[ckausihan_12]

Hi @Greg_Deckler , in baseline person1 scored 4 and and in aut  person1 scored 5, meaing they made a progress of +1. I have cleared up my example tables a little.