Calculating through unique values

[Anonymous]

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

[rubayatyasmin · Accepted 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]
)

 

[Anonymous]

It works exactly as intended, thank you @rubayatyasmin 

[rubayatyasmin]

Happy to help