Totals showing as 0 instead of the sum

[Anonymous]

Hi, I am having issued with my formula not summing up at the Total and Subtotal level. I would like to show me the sum in the subtototals and totals instead of Zeros (Because it is doing it linear) 

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ，

According to your description, I create a sample.

RR23 and Average RR23 are all measures.

RR23 = MAX('Table'[Sales])

Average RR 23 = MAX('Table'[Cost])

Here's my solution, create a measure.

Measure =
SUMX (
    ADDCOLUMNS (
        'Table',
        "Diff",
            IF ( [RR23] > [Average RR 23], [RR23] - [Average RR 23], 0 )
    ),
    [Diff]
)

Get the correct result.

Best Regards,
Community Support Team _ kalyj

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

[ryan_mayu]

@Anonymous 

pls try this

opportunity 2= IF ( ISFILTER (category), Opportunity, 0)

[Anonymous]

Hi,
it is not working. I am using the following formula but it does not seems to be calculating correcly. 

VAR VendorData =

    SUMMARIZE(

        '1P_MainDataSet',

        '1P_MainDataSet'[Vendor],

        "AvgReturnAmt", [Returns $@AVG]-[Return Amount]

    )

RETURN

SUMX( VendorData, [AvgReturnAmt] )

This is how it looks:

what I essentially need is for the opportunity column to be = Return amount - Return $@ avg... but it is not doing it. 

[ryan_mayu]

could you pls provide the sample data and expected output?