Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Help on Formula

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

1 ACCEPTED SOLUTION
v-deddai1-msft
Community Support
Community Support

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

View solution in original post

7 REPLIES 7
v-deddai1-msft
Community Support
Community Support

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
Community Champion
Community Champion

@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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
Super User

@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
Not applicable

@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
Super User
Super User

@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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors