Getting AVERAGE to ignore "0" and blanks

[rjsidek]

Hi everyone,

I have a dataset that looks a bit like this

 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

[Rygaard]

would try a Filter :

Column = CALCULATE(AVERAGE('Table (2)'[number]);FILTER('Table (2)';'Table (2)'[number]>0))

you can also use filter in same wain in a messure

[rjsidek]

@parry2k 

ANy chance you could help me with this issue?

[v-lid-msft]

Hi @rjsidek ,

We can try to use the following measure to meet your requiremnet:

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

Best regards,

[rjsidek]

@v-lid-msft 

Hi, thanks for your reply. The formula almost works, but is not exactly what I want it to be. I was wondering if you could help me figure it out. 

Using the DAX you suggested, this is the result:

It is ignoring 0s which is great! But, If i were to put this in a card visualization, it would return the value 57.5. This is because it is taking an average of the 2 average entries from the above 2 rows. This is not what I want. I want it to show for only the latest entry. Thus, the desired output is for it to only show the second row, where the deal size max and deal size min is 100 and 50 respectively. This is because that is the latest entry for the 2 deal size columns. The desired output for average would be 75.

How would i edit the code to get it working that way?

Thank you so much for your help

[v-lid-msft]

Hi @rjsidek ,

We can try to use the following measures to meet your requirement:

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

Best regards,

[rjsidek]

@v-lid-msft 

This is the code I used, basically same as your code just the parameters were tweaked to fit my dataset

Average Deal Size test = 
VAR latestd =
    MAX ( Table1[Date of Event] )
VAR cname =
    CALCULATE (
        MAX (Table1[Company Name] ),
        FILTER (
            Table1,
            Table1[Date of Event] = latestd
                && Table1[Deal Size (Max)] <> 0
                && Table1[Deal Size (Min)] <> 0
        )
    )
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
    )

And this is a screenshot of pbi after applying this DAX

For some reason, now the total is gone. The end goal is to show the value in a card visualization, which is now just showing up as a blank. Any ideas how to rectify this?

[v-lid-msft]

Hi @rjsidek ,

Sorry for our mistake, could you please try to use the following formula?

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
    )

Best regards,

[rjsidek]

Hi @v-lid-msft 

So now, it is showing the average, but the value it shows is an average of all the previous  entries

 Not sure why this would be the case when the measure is asking it to return based on Max Date. Is there something else that I could try? The number I want in the card visualization would be only the latest one, so the desired output would be 75

[v-lid-msft · Accepted solution]

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,

[amitchandak]

Try like

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

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[rjsidek]

Hi @amitchandak 

Thanks for replying.

The issue still persists. The total average at the bottom is still taking into account those entries where the Average deal size is 0. This then skews the data where the total is divided by the count that includes entries which are 0.