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
MV_1306
Frequent Visitor

Sum up the values in a column only once if they are repeated and cumulative measure

Hi all

 

I have two tables. In the first table "Contract Management" I have a list of contracts each with a unique ID  ("ID" ) and "Original Contract Value". In my second table "Changes Contracts" I have a record of changes "Cost of Changes" occuring for each contract. I used the Related function to extract information from Table 1-"Contract Management" which i have used in other parts of my report. 

 

 Table 2:Changes ContractsTable 2:Changes ContractsTable 1:Contract ManagementTable 1:Contract Management

 

 

 

 

 

I would like to create two measures:

 

1. I would like to have a measure that shows the sum of the original contract values 

  • So if i select Contract Number 1 and 2 this measure should show 40 (30+10)

 

2. I would then like to have a cumulative measure that takes the Original contract value for each project and adds the changes.

  • If Contract Number 1 is selected it should read 17  (i.e.10+1+2+4) 
  • Like wise if Contract Number 1 and 2 are selected it should read  51 (i.e. 30+10+1+2+4+2+1+1)

 

1 ACCEPTED SOLUTION

So, I built your two tables exactly as you showed and created a relationship between "Contract Number" columns. I then used Contract Number from the Contract Management table for my slicer. If there is more to the model, let me know, otherwise kind of flying blind.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

How about this:

 

Total = SUM('Contract Management'[Original Contract Value]) 

Cumulative Total = SUM('Contract Management'[Original Contract Value]) + SUM('Changes Contracts'[Cost of Changes])

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi,

 

Unfortunately I can not directly sum from the Contract Management table , due to the relationships in my model, the measure does not update with the filters.

 

 

So, I built your two tables exactly as you showed and created a relationship between "Contract Number" columns. I then used Contract Number from the Contract Management table for my slicer. If there is more to the model, let me know, otherwise kind of flying blind.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi, That worked as required, I was using my slicers from my Change Contracts Table instead. Thank you for your assisstance,

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.