Calculate Net Value Based on Criteria in Different Column

[GMadd]

I need either a calculated column or a measure to give me the net value based on if Delivery in my table as a TU partner assigned or not. End result is to have a table visual that displays the Net Value of deliveries that have TU Partner assigned along with the count of the deliveries  and also the Net value of deliveries with TU partner assigned and the count of those deliveries.  My data table looks like this below

Delivery	TU partner	Net value
88044253		$63,329
88044210		$9,452
88044245		$28,372
88047823		$4,638
88048741	UPSG	$287
88048765	UPSG	$428
88048979	FDEG	$154

My end result for my table visial should look something like below.

Value of Deliveries No TU Partner	Count of Deliveries No TU Partner	Value of Deliveries TU Partner	Count of Deliveries TU Partner
101,153	3	$5,507	4

I tried to use a calculated column below but when I created another column to give me the value for the deliveries that had a TU Partner it gave me a circular reference error.

Value No Carrier Assigned = CALCULATE(SUM('Daily Tracking'[Net value]),('Daily Tracking'[TU partner] =""))

Thanks for your help

[v-kongfanf-msft]

Hi @GMadd ,

You can created below calculated table:

Table 2 = 
VAR no_TU =
    CALCULATE ( SUM ( 'Table'[Net value] ), 'Table'[TU partner] = BLANK () )
VAR TU =
    CALCULATE ( SUM ( 'Table'[Net value] ), 'Table'[TU partner] <> BLANK () )
VAR count_no_TU =
    CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[TU partner] = BLANK () )
VAR count__TU =
    CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[TU partner] <> BLANK () )
RETURN
    SUMMARIZE (
        'Table',
        "Value of Deliveries No TU", no_TU,
        "Count of Deliveries No TU", TU,
        "Value of Deliveries TU", count_no_TU,
        "Count of Deliveries TU", count__TU
    )

Best Regards,
Adamk Kong

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

[hansontm]

You could add a calculated column as follows:

TU Partner Flag = IF(ISBLANK('Daily Tracking'[TU partner]), "N", "Y")

Then build out your metrics as calculated measures:

Value of deliveries no TU Partner = CALCULATE(SUM('Daily Tracking'[Net value]),('Daily Tracking'[TU Partner Flag] ="N"))

Count of deliveries no TU Partner = CALCULATE(COUNT('Daily Tracking'[Net value]),('Daily Tracking'[TU Partner Flag] ="N"))

Value of deliveries TU Partner = CALCULATE(SUM('Daily Tracking'[Net value]),('Daily Tracking'[TU Partner Flag] ="Y"))

Count of deliveries TU Partner = CALCULATE(COUNT('Daily Tracking'[Net value]),('Daily Tracking'[TU Partner Flag] ="Y"))

[GMadd]

hansontm,

i tried the calculated column but it returns a value of Y for every cell in TU partner if it as data in it or not.

Any idea why?

[GMadd]

I figured out that the blank value needs to be null for isblank to work

[v-kongfanf-msft · Accepted solution]

Hi  @GMadd ,

ISBLANK function for the text type and numerical type of the field there is a difference in the results of the judgment, refer to the following:

result_ = IF(ISBLANK('Table'[Column2]),1,0)

Best Regards,
Adamk Kong

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