Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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 ID | QuoteID | Valid | ProductID | Quantity | Product price | Product value | Quote value |
OID_001 | QID_100 | FALSE | PID_900 | 1 | 50 | 50 | 600 |
OID_001 | QID_100 | FALSE | PID_901 | 5 | 100 | 500 | 600 |
OID_001 | QID_100 | FALSE | PID_902 | 5 | 10 | 50 | 600 |
OID_001 | QID_101 | TRUE | PID_901 | 10 | 100 | 1000 | 1050 |
OID_001 | QID_101 | TRUE | PID_902 | 5 | 10 | 50 | 1050 |
OID_002 | QID_200 | TRUE | PID_903 | 10 | 40 | 400 | 400 |
OID_002 | QID_201 | FALSE | PID_903 | 5 | 40 | 200 | 200 |
OID_003 | QID_300 | TRUE | PID_901 | 10 | 100 | 1000 | 1000 |
OID_004 | QID_400 | TRUE | PID_905 | 10 | 5 | 50 | 1550 |
OID_004 | QID_400 | TRUE | PID_910 | 10 | 150 | 1500 | 1550 |
OID_004 | QID_401 | FALSE | PID_905 | 50 | 5 | 250 | 290 |
OID_004 | QID_401 | FALSE | PID_906 | 2 | 20 | 40 | 290 |
Solved! Go to Solution.
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]
)
Proud to be a 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]
)
Proud to be a Super User!
Happy to help
Proud to be a Super User!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
76 | |
74 | |
57 | |
38 | |
33 |
User | Count |
---|---|
71 | |
66 | |
57 | |
49 | |
47 |