Forum Discussion

Michael1's avatar
Michael1
Advocate II
9 years ago
Solved

Calculating a Weighted Performance using DAX

Hello Power BI Community,

 

I am trying to calculate a weighted measure where the result is a single value.

 

This is student assessment data where there are four possible levels that a student might have achieved (beginning, devloping, proficient or distinguished) where each of these levels needs to be weighted (beginning=0, developing=0.5, proficient=1.0, distinguished=1.5).

 

To accomplish this (without DAX), I simply determine the number of students at each level and then multiply them by the value (above) and lastly divide by the number of students assessed.

 

To complicate this formula--I have duplicate records, so I need to filter by performance level and by a distinct Student ID.

 

If you are willing, please take a look at the code below and offer suggestions.

 

Bmk 2 Retest 4 5 ES Weighted Performance =
VAR Beginning =
    CALCULATE (
        COUNTROWS ( 'Bmk 2 Retest 4 5 ES' ),
        FILTER ( 'Bmk 2 Retest 4 5 ES', DISTINCT ( 'Bmk 2 Retest 4 5 ES'[StudentID] ) ),
        FILTER (
            'Bmk 2 Retest 4 5 ES',
            'Bmk 2 Retest 4 5 ES'[performance_level] = "Beginning"
        )
    )
VAR Developing =
    CALCULATE (
        COUNTROWS ( 'Bmk 2 Retest 4 5 ES' ),
        FILTER ( 'Bmk 2 Retest 4 5 ES', DISTINCT ( 'Bmk 2 Retest 4 5 ES'[StudentID] ) ),
        FILTER (
            'Bmk 2 Retest 4 5 ES',
            'Bmk 2 Retest 4 5 ES'[performance_level] = "Developing"
        )
    )
VAR Proficient =
    CALCULATE (
        COUNTROWS ( 'Bmk 2 Retest 4 5 ES' ),
        FILTER ( 'Bmk 2 Retest 4 5 ES', DISTINCT ( 'Bmk 2 Retest 4 5 ES'[StudentID] ) ),
        FILTER (
            'Bmk 2 Retest 4 5 ES',
            'Bmk 2 Retest 4 5 ES'[performance_level] = "Proficient"
        )
    )
VAR Distinguished =
    CALCULATE (
        COUNTROWS ( 'Bmk 2 Retest 4 5 ES' ),
        FILTER ( 'Bmk 2 Retest 4 5 ES', DISTINCT ( 'Bmk 2 Retest 4 5 ES'[StudentID] ) ),
        FILTER (
            'Bmk 2 Retest 4 5 ES',
            'Bmk 2 Retest 4 5 ES'[performance_level] = "Distinguished"
        )
    )
VAR NumberAssessed =
    CALCULATE (
        COUNTROWS ( 'Bmk 2 Retest 4 5 ES' ),
        FILTER ( 'Bmk 2 Retest 4 5 ES', DISTINCT ( 'Bmk 2 Retest 4 5 ES'[StudentID] ) )
    )
RETURN
    CALCULATE (
        ( ( beginning * 0 )
            + ( developing * 0.5 )
            + ( proficient * 1.0 )
            + ( distinguished * 1.5 ) )
            / NumberAssessed
    )

 

When I try to display the measure in a Card I receive this error:

 

Your help is greatly appreciated,

 

Michael

  • Instead of trying to use Distinct() within in the Filter() function, can you try to use it directly inside the Countrows() function like,

     

    COUNTROWS ( DISTINCT ( 'Bmk 2 Retest 4 5 ES'[StudentID] ))

     

    Let me know if this helps.

     

    Oh, and why do you even calculate the value for Beginning if it is gonna be multiplied with zero ? :smileywink: 

5 Replies

  • Mi2n's avatar
    Mi2n
    Microsoft Employee

    Instead of trying to use Distinct() within in the Filter() function, can you try to use it directly inside the Countrows() function like,

     

    COUNTROWS ( DISTINCT ( 'Bmk 2 Retest 4 5 ES'[StudentID] ))

     

    Let me know if this helps.

     

    Oh, and why do you even calculate the value for Beginning if it is gonna be multiplied with zero ? :smileywink: 

      • Mi2n's avatar
        Mi2n
        Microsoft Employee

        Just a small update. Instead of both the CountRows() and Distinct() function, the single Countdistinct() function will also work.