Forum Discussion

GMadd's avatar
GMadd
Icon for Helper I rankHelper I
2 years ago
Solved

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

DeliveryTU partnerNet value
88044253 $63,329
88044210 $9,452
88044245 $28,372
88047823 $4,638
88048741UPSG$287
88048765UPSG$428
88048979FDEG$154

 

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

Value of Deliveries No TU PartnerCount of Deliveries No TU PartnerValue of Deliveries TU PartnerCount of Deliveries TU Partner
101,1533$5,5074

 

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
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

5 Replies

  • hansontm's avatar
    hansontm
    Icon for Resolver I rankResolver I
    2 years ago

    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's avatar
      GMadd
      Icon for Helper I rankHelper I
      2 years ago

      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's avatar
        GMadd
        Icon for Helper I rankHelper I
        2 years ago

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

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.