Splitting a net column into positive and negative numbers in a table

[jh_hog]

Hi, I have data in the folowing format (which alas I can't change)

 

I would like to be able to get a table visualtion like this in PowerBi:

 In other words if the sum of all of the "Net Total" entries for a job is postiive it will be an "Accrued" amount for that job, and if it is negative it is "Deferred" for that job. 

So far I can get the split to work on a line by line basis by using the following measure formulas:

Accrued = if(sum(Query1[Net Total]) > 0,sum(Query1[Net Total]),0)

Deferred = if(SUm(Query1[Net Total]) < 0,sum(Query1[Net Total]),0)

Resulting in this table:

But here lies the problem, the grand total in the table does not work, and putting the measure into a card doesn't either. I think it is because when working out the grand total it is looking to see if the total of all lines is positive or negative then applying the formula, rather than evaluating the net of each job and then summing up this evaluated net. 

It feels like a measure is the way to go because we want to be able to use a slicer to vary the entry date range, but it is not much use if the total does not add up. What am I doing wrong, or is there a better way? Many thanks in advance.

[BhaveshPatel]

Hi @jh_hog

I have solved the problem using Query Editor. follow the screenshots for the solution.

GroupbyConditional column 1Conditional Column 2Load the Query and drag the fields to the canvas

[jh_hog]

Hi Bhavesh, many thanks, unfortunately that does not quite achieve what I need as I also want the date column to be in the query so I can use a slicer, for example If I select a date range of 1/1/2016 to 6/6/2016 I would expect to see the following:

Company Number	Job Number	Net Total	Accrued	Deferred
200	200001	-40	0	-40
200	200002	-465	0	-465
			TOTAL:	-505

And likewise, if it was 1/1/2016 to 5/5/2016, i would expect to see:

Company Number	Job Number	Net Total	Accrued	Deferred
200	200001	-90	0	-90
200	200002	-480	0	-480
			TOTAL:	-570

.

Mike - I tried this too, but unfortunatly it still does not show the correct total.

Thanks, James

[BhaveshPatel]

Hi James,

Try this. 

Firstly, I created a conditional column as per the below screenshot.

Secondly, I created a conditional column for accrued hide column as per the solution presented in first Reply.

If Net Total is greater than 0 Then Net Total Otherwise 0 . I called this column accrued Hide.

Thirdly, I created a conditional column for deferred as per the solution presented in the first Reply.

Deferred = if Net Total is less than 0 Then Net Total otherwise 0

Load the Query 

Create a new measure  

This will give you the final expected results.

[mmanwaring]

Hi

I would make a calculated column for Accrued and Deferred and a measure to sum those columns

Regards

Mike