Help on Formula

[Anonymous]

Hello, 

I am using this below formula but it is showing error. 

Scorecard 2 = CALCULATE([Scorecard],IF(NOT('Sheet1'[Capability & offer] = BLANK() && NOT(Sheet1[Overall Performance] = BLANK() && NOT(Sheet1[Delivering with integrity] = BLANK() && NOT(Sheet1[Communicating effectively] = BLANK() && NOT(Sheet1[Demonstrating right behaviours] = BLANK() && NOT(Sheet1[Knowing expectations] = BLANK() && NOT(Sheet1[Health, safety & wellbeing] =BLANK() && NOT(Sheet1[Resolving issues] =BLANK())))))))),0))

scorecard = 

(AVERAGE(Sheet1[Capability & offer])+AVERAGE(Sheet1[Communicating effectively])+AVERAGE(Sheet1[Delivering with integrity])+AVERAGE(Sheet1[Demonstrating right behaviours])+AVERAGE(Sheet1[Health, safety & wellbeing])+AVERAGE(Sheet1[Knowing expectations])+AVERAGE(Sheet1[Overall Performance]))/7

So basically the problem I am facing is - I have 7 columns from which I am calculating scorecard ( adding the average of those columns and then divide by 7). I get the desired result. But there are some discrepancy in my data , like all the 7 columns does not contain a value for example 3 out of 7 columns may have a value and other 4 may not by this by average result comes out to be wrong. 

If I can write a formula, if any column is blank do not calculate the scorecard value row. I see data yearly so we have to write measure for it, making a new column will not work.

Please let me know to tackle this problem. 

thanks in advance

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ，

Would you please try to use the following measure:

Scorecard 2 =
VAR A =
    CALCULATE (
        AVERAGE ( 'Sheet1'[Capability & offer] ),
        FILTER ( 'Sheet1', NOT ( ISBLANK ( 'Sheet1'[Capability & offer] ) ) )
    )
VAR B =
    CALCULATE (
        AVERAGE ( 'Sheet1'[Overall Performance] ),
        FILTER ( 'Sheet1', NOT ( ISBLANK ( 'Sheet1'[Overall Performance] ) ) )
    )
VAR C =
    CALCULATE (
        AVERAGE ( 'Sheet1'[Delivering with integrity] ),
        FILTER ( 'Sheet1', NOT ( ISBLANK ( 'Sheet1'[Delivering with integrity] ) ) )
    )
VAR D =
    CALCULATE (
        AVERAGE ( 'Sheet1'[Communicating effectively] ),
        FILTER ( 'Sheet1', NOT ( ISBLANK ( 'Sheet1'[Communicating effectively] ) ) )
    )
VAR E =
    CALCULATE (
        AVERAGE ( 'Sheet1'[Demonstrating right behaviours] ),
        FILTER (
            'Sheet1',
            NOT ( ISBLANK ( 'Sheet1'[Demonstrating right behaviours] ) )
        )
    )
VAR F =
    CALCULATE (
        AVERAGE ( 'Sheet1'[Knowing expectations] ),
        FILTER ( 'Sheet1', NOT ( ISBLANK ( 'Sheet1'[Knowing expectations] ) ) )
    )
VAR G =
    CALCULATE (
        AVERAGE ( 'Sheet1'[Health, safety & wellbeing] ),
        FILTER ( 'Sheet1', NOT ( ISBLANK ( 'Sheet1'[Health, safety & wellbeing] ) ) )
    )
RETURN
     ( A + B + C + D + E + F + G ) / 7

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[PaulDBrown]

@Anonymous 

Are you totally sure you should be adding averages? (Off the top of my head...)

as @Ashish_Mathur  states, it looks like you need to think about data structure in your model.

please provide a sample dataset or PBIX file to help us help you

[Ashish_Mathur]

Hi,

Observing your formula, it looks like your data needs restructuring.  Share your source dataset, explain the business context and show the expected result.

[amitchandak]

@Anonymous , I think with blank you have use == or isblank([column])

CALCULATE([Scorecard],IF(NOT('Sheet1'[Capability & offer] == BLANK() && NOT(Sheet1[Overall Performance] == BLANK() && NOT(Sheet1[Delivering with integrity] == BLANK() && NOT(Sheet1[Communicating effectively] == BLANK() && NOT(Sheet1[Demonstrating right behaviours] == BLANK() && NOT(Sheet1[Knowing expectations] == BLANK() && NOT(Sheet1[Health, safety & wellbeing] ==BLANK() && NOT(Sheet1[Resolving issues] ==BLANK())))))))),0))

[Anonymous]

@amitchandak  this is error I am getting after your formula.

The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression.

[Greg_Deckler]

@Anonymous What is your error? Can you post sample data? Use daxformatter.com. You need to use a FILTER clause and not IF, FILTER(NOT(ISBLANK([capability & offer])) && NOT(ISBLANK(...

Scorecard 2 =
CALCULATE (
    [Scorecard],
    IF (
        NOT (
            'Sheet1'[Capability & offer] = BLANK ()
                && NOT (
                    Sheet1[Overall Performance] = BLANK ()
                        && NOT (
                            Sheet1[Delivering with integrity] = BLANK ()
                                && NOT (
                                    Sheet1[Communicating effectively] = BLANK ()
                                        && NOT (
                                            Sheet1[Demonstrating right behaviours] = BLANK ()
                                                && NOT (
                                                    Sheet1[Knowing expectations] = BLANK ()
                                                        && NOT (
                                                            Sheet1[Health, safety & wellbeing] = BLANK ()
                                                                && NOT ( Sheet1[Resolving issues] = BLANK () )
                                                        )
                                                )
                                        )
                                )
                        )
                )
        ),
        0
    )
)





scorecard =
(
    AVERAGE ( Sheet1[Capability & offer] )
        + AVERAGE ( Sheet1[Communicating effectively] )
        + AVERAGE ( Sheet1[Delivering with integrity] )
        + AVERAGE ( Sheet1[Demonstrating right behaviours] )
        + AVERAGE ( Sheet1[Health, safety & wellbeing] )
        + AVERAGE ( Sheet1[Knowing expectations] )
        + AVERAGE ( Sheet1[Overall Performance] )
) / 7

[Anonymous]

Hello 

Thanks for your solution but it is not working.

its is giving this error - The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression.