summing group by x, but exclude inner negative group by y

[ovonel]

I have a measure

Rev :=
CALCULATE (
    SUM ( Revenue[Revenue] ),
    FILTER ( 'Center', NOT 'Center'[Segment] IN { "xyz" } )
)

And then I use the measure to do a total per customer as:

TotalCustomer :=
SUMX ( SUMMARIZE ( Revenue, 'Customer'[CustomerNum] ), Rev )

But I need to exclude specific revenue…

I should exclude when....  whenever the sum, grouped by ‘Center’[Segment] Is negative, only in that case it should be excluded, how can I achieve that? (can it be done without an intermediate measure)

To give a dummy example, when summing by customer I shall:

if column A represents Segment...

For a given customer:

I shall not sum segment A since its sum is -2, and I shall sum segment B, I shall sum it as 25 (including the -1 !),

[v-xulin-mstf · Accepted solution]

Hi @ovonel,

Try measure as:

Measure = 
var _sum=
SUMX(
    FILTER(
        ALL('Table'),
        'Table'[A]=MAX('Table'[A])
    ),
    'Table'[B]
)
return
IF(
    _sum>0,
    _sum,
    BLANK()
)

Here is the output:

If you still have some question, please don't hesitate to let me known.‌‌

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[selimovd]

Hey @ovonel ,

does [Rev] return the total per segment?

If yes then you can check that within the SUMX:

TotalCustomer :=
SUMX ( SUMMARIZE ( Revenue, 'Customer'[CustomerNum] ), IF([Rev] >= 0, [Rev] ) )

If that's not the case you should give more information. How is the segment table connected to the rest? Maybe share a sample file or something like that.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic