Help with Filter Formula

[Anonymous]

Hi All,

I have a query that pulls in our sales pipeline from Salesforce which contains many opportunities that can have multiple rows. Each opportuinity has a unique id.

What I am trying to do is create a calculated table that has the Opportunity IDs of all deals where the total deal value is >=10

I have come up with the following formula that nearly works, but for some reason not quite. There are a few deals that are missing, and I don't know why. I think that maybe my table formula isn't doing what I think it is. Any help guidance with either why my formula isn't working, or what a better formula would be will be greatly appreciated. Thank you.

Here is my formula

Pipe TCV >10m = SUMMARIZE(
    CALCULATETABLE(
        FILTER('2 Pipeline','2 Pipeline'[Pipe CV €M]>=10)),
'2 Pipeline'[Opportunity ID])

[v-jiascu-msft · Accepted solution]

Hi @Anonymous ,

That's because you filtered the row rather than the total of each Opportunity ID. Please try these two measures.

Pipe TCV >10m =
SUMMARIZE (
    CALCULATETABLE (
        FILTER (
            '2 Pipeline',
            CALCULATE (
                SUM ( '2 Pipeline'[Pipe CV €M] ),
                ALLEXCEPT ( '2 Pipeline', '2 Pipeline'[Opportunity ID] )
            ) >= 10
        )
    ),
    '2 Pipeline'[Opportunity ID]
)

Table =
SELECTCOLUMNS (
    FILTER (
        SUMMARIZE (
            '2 Pipeline',
            '2 Pipeline'[Opportunity ID],
            "Total", SUM ( '2 Pipeline'[Pipe CV €M] )
        ),
        [Total] >= 10
    ),
    "Opportunity ID", [Opportunity ID]
)

Best Regards,

[Anonymous]

Perfect! Thank you.