Cohort analysis

Hi Ashish_Mathur ,

 

I believe I was able to do the matrix you need:

 

In the example you have you get 20 persons gettting ratings of 4 and 5 for overall perception base on the image you provided.

I have done the check for Intructor Efecctives and I got this result:

 

This means that I have values that may cross over in ters of Rating 2,3,4,5 of Instructor and Rating 4,5 of the Overall.

 

If we create two disconnected tables with the ratings, this will allow to have use of the rating in the matrix and get additional context for calculation and add the following measure:

Comparition of ratings = 
VAR RatingComparative =
    FILTER (
        ADDCOLUMNS (
            Students,
            "RatingCalculation",
                CALCULATE (
                    _Measures[Measure],
                    'Rating buckets'[Rating] = SELECTEDVALUE ( RankingComp[Rating] ),
                    'Objective questions grouping'[Categories]
                        = SELECTEDVALUE ( 'Objective questions grouping_dup'[Categories] )
                )
        ),
        [RatingCalculation] <> BLANK ()
    )
VAR RatingComparativeDub =
    FILTER (
        ADDCOLUMNS (
            Students,
            "RatingCalculation",
                CALCULATE (
                    _Measures[Measure],
                    'Rating buckets'[Rating] = SELECTEDVALUE ( rankingcomp2[Rating] ),
                    'Objective questions grouping'[Categories]
                        = SELECTEDVALUE ( 'Objective questions grouping'[Categories] )
                )
        ),
        [RatingCalculation] <> BLANK ()
    )
RETURN
    COUNTROWS ( NATURALINNERJOIN ( RatingComparative, RatingComparativeDub) ) + 0

 

This will return the following values:

 

Has you can see the total number of persons is 40.

 

I haven't double check the results, but looking at the measure you have calculated this matches.

 

See PBIX file attach.