cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## Calculate Net Value Based on Criteria in Different Column

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] =""))

1 ACCEPTED SOLUTION
Community Support

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,

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

5 REPLIES 5
Community Support

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,

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

Regular Visitor

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"))

Helper I

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?

Helper I

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

Community Support

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,

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

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors