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

    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: