Need help to write a measure and solve this grouping challenge

[hcova]

Hi All,

I have P/L results of a group of sellers. Sometimes they make deals with losses (P/L negative in the below table)

I need the following: For each country I need the sum of net incomes of those sellers with positive profits. And of course, the sum of Net Incomes of sellers with losses.

Let me clarify this with a example taken from this table.

In US you can realize that Bob, Jim and Mai had positive net profits only.   Bob did a net income of $70, Mai a net income of $20 and Jim a net income of $17.    So, the sum of sellers with positive net profits was $70+$20+$17 = $107. 

Following the same logic , in US the sum of seller with net losses was -$40. 

Therefore I need to build Power BI Measures to get the $107 and -$40 sums above mentioned for US and the same calculations for CAN.

Best Regards

[Ashish_Mathur · Accepted solution]

Hi,

Try this

Positive net income=SUMX(FILTER(SUMMARIZE(VALUES(Data[Seller]),[Seller],"ABCD",SUM(Data[P/L])),[ABCD]>0),[ABCD])

Negative net income=SUMX(FILTER(SUMMARIZE(VALUES(Data[Seller]),[Seller],"ABCD",SUM(Data[P/L])),[ABCD]<0),[ABCD])

If this does not help, then share the link from where i can download your PBI file.

[Ashish_Mathur]

Hi,

Drag Country to the row labels of the visual and write these measures

Positive net income=CALCULATE(SUM(Data[P/L]),FILTER(VALUES(Data[Seller]),SUM(Data[P/L])>0))

Negative net income=CALCULATE(SUM(Data[P/L]),FILTER(VALUES(Data[Seller]),SUM(Data[P/L])<0))

Hope this helps.

[hcova]

Hi Ashish. Thanks a lot for your answer.
However the measures you wrote give wrong results.

Let me explain it with the above pivot tables:

For the country CAN the sum of positive net income is 252.
Your measure "Positive Net Income" gives 215 (see last PT) instead of 252, because it is summing all CAN P/L values, including those negatives values (i.e 215 =137+77+38-5-10-22).  "Positive Net Income" measure should sum all positives only (=137+77+38 = 252) 

The similiar issue occurs with "Negative Net Income" measure.

Best Regards

[Ashish_Mathur · Accepted solution]

Hi,

Try this

Positive net income=SUMX(FILTER(SUMMARIZE(VALUES(Data[Seller]),[Seller],"ABCD",SUM(Data[P/L])),[ABCD]>0),[ABCD])

Negative net income=SUMX(FILTER(SUMMARIZE(VALUES(Data[Seller]),[Seller],"ABCD",SUM(Data[P/L])),[ABCD]<0),[ABCD])

If this does not help, then share the link from where i can download your PBI file.

[hcova · Accepted solution]

Dear Ashish:

Amazing. I appreciate you time and effort.

Any link about where I can learn about this advanced solution?
Thanks a lot!

[Ashish_Mathur]

You are welcome.  Start with reading articles on each of those functions on the MSDN wesbite.