Forum Discussion

powerbiss's avatar
powerbiss
Helper I
4 years ago
Solved

Calculate sum without considering the duplicate values in another column

Hi Team,
I have 3 columns (lead_id, stage_id and expected_revenue). I want to calculate the sum of expected_revenue for each stage_id, but the lead_id has a duplicate value. So I want to sum the expected_revenue without considering the duplicate value in the lead_id column.

 

Thanks for all your help. Really appreciate your time.

 

 

Need Help

7 Replies

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    powerbiss 

    Measure =
SUMX (
    SUMMARIZE ( 'Table', 'Table'[stage_id], 'Table'[expected_revenue] ),
    'Table'[expected_revenue]
)
  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    powerbiss try this measure:

     

    Sum =
SUMX ( SUMMARIZE ( YourTable, YourTable[StageId], YourTable[LeadId], "@Value", MAX ( YourTable[ExpectedRevenue] ), [@Value] )

     

    ✨ Follow us on LinkedIn

     

    Learn about conditional formatting at Microsoft Reactor

    My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

    • powerbiss's avatar
      powerbiss
      Helper I
      4 years ago

      Hi Team,

      Really appreciate your time and help. Both measure has worked for me. I have another issue with the same data. If the stage_id = 1 and the expected_revenue = 0 then the stage is Open Leads, and if the expected_revenue is greater than 0 the stage is New Opportunities. Please see the below desired result.

       

      Again really appreciate your time and help.

       

       

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    powerbiss add a new column using following DAX:

     

    Status Columns = 
IF ( YourTable[Stage_Id] = 1 && YourTable[expected_revenue] = 0,  "Open Leads", "New Opportunities" )

     

    ✨ Follow us on LinkedIn

     

    Learn about conditional formatting at Microsoft Reactor

    My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    Do you use those duplicated rows in certain situations or would it make sense to just remove duplicates entirely (say, in the Query Editor) before loading the table?

    • powerbiss's avatar
      powerbiss
      Helper I
      4 years ago

      Hey AlexisOlson,

      After going through the dataset (from Postgresql database), looks like I would need those duplicate rows to calculate some other values.

       

      Thanks

       

  • Happy10Eggs's avatar
    Happy10Eggs
    New Member
    3 years ago

    Hi All,

     

    I need your assistance. I have connected to Salesforce to get a live feed data on opportunity objects, but Opportunity amount seem to duplicate whenever I get the Opp Name, Owner & Team member.

     

    This is the data I get from Salesforce (Opportunity Owner, Team Member Name, Opportunity Name and Amount). I want to calculate the sum of Amount for each Opportunity, but the Opportunity Name has a duplicate value. So I want to sum the Amount without considering the duplicate value in the Opportunity Name column.

     

    Thanks for all your help. Really appreciate your time.