Forum Discussion

ckausihan_12's avatar
ckausihan_12
Icon for Helper I rankHelper I
6 years ago
Solved

Compare Rows

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 p...
Need Help
Show and Tell
Tutorial Requests
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    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's avatar
v-zhenbw-msft
Icon for Community Support rankCommunity Support
6 years ago

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's avatar
ckausihan_12
Icon for Helper I rankHelper I
6 years ago

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.