Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
rjsidek
Helper II
Helper II

Getting AVERAGE to ignore "0" and blanks

‎01-12-2020 08:45 PM

Hi everyone,

 

I have a dataset that looks a bit like this

For question.png

 For Deal Size Min and Max, it is in the dataset itself which is an excel sheet. The Average deal size is a measure that I have created. This is the DAX i wrote to get the Average Deal Size.

Average Deal Size test = CALCULATE(AVERAGE('PE Direct DP Form'[Deal Size (Max)]) + AVERAGE('PE Direct DP Form'[Deal Size (Min)]))/2

 

As you can see from the above picture, the total average is taking into account those where the average deal size is 0. This then skews the average. Is there a way to modify this measure to not take instances where the average deal size is 0 or blank?

 

Thanks

Labels:
Message 1 of 12
5,048 Views
0
1 ACCEPTED SOLUTION
v-lid-msft
Community Support
Community Support
In response to rjsidek

‎01-14-2020 10:42 PM

Hi @rjsidek ,

 

The purpose we use maxdate because we want to get the latest record for the company, we should add both date and company name into the filter, could  you please try to use the following measure?

 

Average Deal Size test =
VAR latestd =
    CALCULATE (
        MAX ( Table1[Date of Event] ),
        FILTER ( Table1, Table1[Deal Size (Max)] <> 0 && Table1[Deal Size (Min)] <> 0 )
    )
VAR cname =
    CALCULATE ( MAX ( Table1[Company Name] ), Table1[Date of Event] = latestd )
RETURN
    CALCULATE (
        DIVIDE (
            CALCULATE (
                AVERAGE ( Table1[Deal Size (Max)] ),
                Table1[Deal Size (Max)] + 0 <> 0
            )
                + CALCULATE (
                    AVERAGE ( Table1[Deal Size (Min)] ),
                    Table1[Deal Size (Min)] + 0 <> 0
                ),
            2
        ),
        'Table1'[Company Name] = cname && 'Table1'[[Date of Event] = latestd
    )


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Message 11 of 12
4,998 Views
11 REPLIES 11

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All