Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
powerbiss
Helper I
Helper I

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.

 

powerbiss_0-1640534915513.png

 

2 ACCEPTED SOLUTIONS
smpa01
Super User
Super User

@powerbiss 

Measure =
SUMX (
    SUMMARIZE ( 'Table', 'Table'[stage_id], 'Table'[expected_revenue] ),
    'Table'[expected_revenue]
)
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

7 REPLIES 7
Happy10Eggs
New Member

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. 

Happy10Eggs_0-1663830712942.png

 

AlexisOlson
Super User
Super User

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?

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
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

smpa01
Super User
Super User

@powerbiss 

Measure =
SUMX (
    SUMMARIZE ( 'Table', 'Table'[stage_id], 'Table'[expected_revenue] ),
    'Table'[expected_revenue]
)
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
parry2k
Super User
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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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.

 

powerbiss_0-1640537349094.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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