Calculate sum without considering the duplicate values in another column

[powerbiss]

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.

[smpa01 · Accepted solution]

@powerbiss 

Measure =
SUMX (
    SUMMARIZE ( 'Table', 'Table'[stage_id], 'Table'[expected_revenue] ),
    'Table'[expected_revenue]
)

[Happy10Eggs]

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. 

[AlexisOlson]

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]

Hey AlexisOlson,

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

Thanks

[parry2k · Accepted solution]

@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.⚡

[smpa01 · Accepted solution]

@powerbiss 

Measure =
SUMX (
    SUMMARIZE ( 'Table', 'Table'[stage_id], 'Table'[expected_revenue] ),
    'Table'[expected_revenue]
)

[parry2k]

@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]

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.