Help Combining Query with Excel

[Andshepch]

Hi all,

I am struggling with something that seems really basic coming from my Excel based background, and I would really appreciate some help. So far I am loving Power BI/DAX and I'm sure I can solve my problem with some guidance.

I have a Query that gets our sales pipeline from Salesforce, and I can summarize that fine in a Table visualisation - the values of the opportunities are set by our sales people. My table then summarise their deals by Opportunity ID (some of the deals contain more than one service) So the Query looks like this:

Opportunity ID	Account	Deal	Service	Value
OP# 00000001	A	E	x	5
OP# 00000001	A	E	y	6
OP# 00000001	A	E	z	7
OP# 00000002	B	F	x	1
OP# 00000002	B	F	y	2
OP# 00000003	A	G	x	5
OP# 00000004	C	H	x	5
OP# 00000005	D	I	y	2
OP# 00000006	E	J	y	3

and the Summary looks like this:

Opportunity ID	Account	Deal	Value
OP# 00000001	A	E	18
OP# 00000002	B	F	3
OP# 00000003	A	G	5
OP# 00000004	C	H	5
OP# 00000005	D	I	2
OP# 00000006	E	J	3
Total			36

However - some of our sales people are a tad optimistic, so leadership want to de-risk our numbers with a manual adjustment based on where they think the value foa a deal should be e.g.

Opportunity ID	Account	Value
OP# 00000001	A	15
OP# 00000006	E	2

This is in the form of an excel file that I have on our Gateway - that they can edit with the values to what they think we should be forecasting on some of the deals

What I would like to get to is a table showing the following:

Opportunity ID	Account	Deal	Value	Adjusted Value
OP# 00000001	A	E	18	15
OP# 00000002	B	F	3	3
OP# 00000003	A	G	5	5
OP# 00000004	C	H	5	5
OP# 00000005	D	I	2	2
OP# 00000006	E	J	3	2
Total			36	32

So show the Adjusted value if there is one, but the unadjusted value if the deal is not on the Excel file.

All the methods that I have tried end up with the Total for the adjusted column being wrong - so it looks like my measure creation isn't correct.

in Excel I would do a lookup, with an error condition. I'm sure this is possible in DAX, but I can't get it to work. CALCULATE hasn't "clicked" for me just yet.

Thanks for any/all help.

Andrew

[v-chuncz-msft · Accepted solution]

@Andshepch,

You may add two measures as shown below.

Measure =
VAR v =
    LOOKUPVALUE (
        Table2[Value],
        Table2[Opportunity ID], SELECTEDVALUE ( Table1[Opportunity ID] ),
        Table2[Account], SELECTEDVALUE ( Table1[Account] )
    )
RETURN
    IF ( ISBLANK ( v ), SUM ( Table1[Value] ), v )

Measure 2 =
SUMX (
    SUMMARIZE ( Table1, Table1[Opportunity ID], Table1[Account], Table1[Deal] ),
    [Measure]
)

[Andshepch]

Sam,

Thank you so much. That works perfectly!

Have a great day!

Regards

Andrew