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

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]))

C 1.jpg

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

C 2.jpg

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

C 3.jpg

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.