Help filtering a measure into 2 separate measures

[CB_LV]

I am using the following measure in power BI to calculate net upsells in some revenue data:

Sum Net Upsell=

/*

See Sum New Customer for detailed information

*/

CALCULATE([AmtUSD minus AmtUSD_pp in Thousands, Annualized]

            ,FILTER(SUMMARIZE(PBI_Transaction

                            , PBI_CustomerKey[CustomerKey]

                            , PBI_DimDate[GLDate].[Year]

                            , PBI_DimDate[GLDate].[Month]

                                , "Sum"

                                     , MAX(PBI_Transaction[AmtUSD])

                                , "Sum_pp"

                                     , [AmtUSD_py Max]

                            )

                    , [Sum_pp] > 5 && [Sum] > 5 

                    )

        )

I am then splitting this out into two separate measures to break out net upsell into the two halves, upsells and downsells, using the two different measures here.

Sum Upgrade =

/*

See Sum New Customer for detailed information

*/

CALCULATE([AmtUSD minus AmtUSD_pp in Thousands, Annualized]

            ,FILTER(SUMMARIZE(PBI_Transaction

                            , PBI_CustomerKey[CustomerKey]

                            , PBI_DimDate[GLDate].[Year]

                            , PBI_DimDate[GLDate].[Month]

                                , "Sum"

                                     , MAX(PBI_Transaction[AmtUSD])

                                , "Sum_pp"

                                     , [AmtUSD_py Max]

                            )

                    , [Sum_pp] > 5 && [Sum] > 5 && [Sum_pp] < [Sum]

                    )

        )

and 

Sum Downgrade =

/*

See Sum New Customer for detailed information

*/

CALCULATE([AmtUSD minus AmtUSD_pp in Thousands, Annualized]

            ,FILTER(SUMMARIZE(PBI_Transaction

                            , PBI_CustomerKey[CustomerKey]

                            , PBI_DimDate[GLDate].[Year]

                            , PBI_DimDate[GLDate].[Month]

                                , "Sum"

                                     , MAX(PBI_Transaction[AmtUSD])

                                , "Sum_pp"

                                     , [AmtUSD_py Max]

                            )

                    , [Sum_pp] > 5 && [Sum] > 5 && [Sum_pp] > [Sum]

                    )

        )

It seems this would work, but the upsell and downsell measures do not tie out to the net upsell measures. Please help. Thanks!

[v-chenwuz-msft · Accepted solution]

Hi @CB_LV ,

Maybe you can try NOT().

Sum Downgrade =

/*

See Sum New Customer for detailed information

*/

CALCULATE([AmtUSD minus AmtUSD_pp in Thousands, Annualized]

            ,FILTER(SUMMARIZE(PBI_Transaction

                            , PBI_CustomerKey[CustomerKey]

                            , PBI_DimDate[GLDate].[Year]

                            , PBI_DimDate[GLDate].[Month]

                                , "Sum"

                                     , MAX(PBI_Transaction[AmtUSD])

                                , "Sum_pp"

                                     , [AmtUSD_py Max]

                            )

                    , [Sum_pp] > 5 && [Sum] > 5 && NOT( [Sum_pp] < [Sum] )

                    )

        )

Best Regards

Community Support Team _ chenwu zhu

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