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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

Calculating through unique values

Hi all, 

 

I have the table below. It shows opportunities, quotes, products and values; this is how data is imported in PowerBI. A particularity of the scenario is that an opportunity can have multiple quotes but only one quote is active/valid per opportunity. One of the issues is that in the total Quote value is replicated in the same row for each quote entry. 

I would like to create a measure which shows the amount of the all valid quotes irrespective of how many products every quote has. In the given example, this amount should be 4000 (the values in Red). However, anything I tried gave me the total column amount which is 6600 (all Valid = True quotes). I feel I'm so close to getting it but it just drives me nuts for a couple of days now. 

PS: I can get the value by working and calculating the quote products but I believe there is a simpler way to do it.

 

Thanks,

 

Opportunity IDQuoteIDValidProductIDQuantityProduct priceProduct valueQuote value
OID_001QID_100FALSEPID_90015050600
OID_001QID_100FALSEPID_9015100500600
OID_001QID_100FALSEPID_90251050600
OID_001QID_101TRUEPID_9011010010001050
OID_001QID_101TRUEPID_902510501050
OID_002QID_200TRUEPID_9031040400400
OID_002QID_201FALSEPID_903540200200
OID_003QID_300TRUEPID_9011010010001000
OID_004QID_400TRUEPID_905105501550
OID_004QID_400TRUEPID_9101015015001550
OID_004QID_401FALSEPID_905505250290
OID_004QID_401FALSEPID_90622040290

 

1 ACCEPTED SOLUTION
rubayatyasmin
Super User
Super User

Hi, @Anonymous 

 

Based on the data given, it seems you want to sum the "Quote value" for each distinct "QuoteID" when "Valid" is TRUE. However, the problem you're encountering is that "Quote value" is repeated for each row of the same "QuoteID". This is causing the sum to be much larger than it should be.

To solve this in Power BI, you can create a measure which first calculates the unique quote values and then sum them up. Here's an example of how you might accomplish this using DAX.

 

Valid Quote Total = 
SUMX(
    SUMMARIZE(
        FILTER('YourTable', 'YourTable'[Valid] = TRUE), 
        'YourTable'[QuoteID], 
        "QuoteValue", MAX('YourTable'[Quote value])
    ), 
    [QuoteValue]
)

 

 rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


View solution in original post

3 REPLIES 3
rubayatyasmin
Super User
Super User

Hi, @Anonymous 

 

Based on the data given, it seems you want to sum the "Quote value" for each distinct "QuoteID" when "Valid" is TRUE. However, the problem you're encountering is that "Quote value" is repeated for each row of the same "QuoteID". This is causing the sum to be much larger than it should be.

To solve this in Power BI, you can create a measure which first calculates the unique quote values and then sum them up. Here's an example of how you might accomplish this using DAX.

 

Valid Quote Total = 
SUMX(
    SUMMARIZE(
        FILTER('YourTable', 'YourTable'[Valid] = TRUE), 
        'YourTable'[QuoteID], 
        "QuoteValue", MAX('YourTable'[Quote value])
    ), 
    [QuoteValue]
)

 

 rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


Anonymous
Not applicable

It works exactly as intended, thank you @rubayatyasmin 

Happy to help

 


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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