Forum Discussion
Helper ICalculate 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.
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.⚡
7 Replies
- parry2k
Super User
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.⚡
powerbissHi 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
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
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
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.
