VLOOKUP from Excel to Power Bi

[Anonymous]

Hi Everyone, 

I am recently new to Power BI and I am struggling on a project. I am trying to use LOOKUPVALUE but can't seem to figure it out. I have confidential data, so I've created example tables with what I am trying to achieve. I am trying to lookup the material code from the sales table and see if it is on the Detail Only Code table. See below tables. 

I would like to eliminate the last two columns in excel and have Power Bi add a calculated column. Essentially, my end goal to to have the revenue from the Detail service alone. I also want the revenue from any sales order that included a Detailing line item. This is where I get the total ticket column. I have pivot tables in excel but want to visualize it on Power BI. 

Please let me know if you have any suggestions for translating these results in Power BI using the first 5 columns. Thanks in advance! 

Here is a link to my spreadsheet. https://docs.google.com/spreadsheets/d/1aLRY67p4U1MWQDpUJSRLZkjLCYwHw9bp/edit?usp=sharing&ouid=10052... 

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

You could create two measures about it.

Sum in = CALCULATE(SUM('Sales Table'[Net Revenue]),FILTER('Sales Table',[Material] in VALUES('Detail Only Code'[Material Code])))

Customersum =
CALCULATE (
    SUM ( 'Sales Table'[Net Revenue] ),
    FILTER (
        'Sales Table',
        [Customer]
            IN SUMMARIZE (
                FILTER (
                    'Sales Table',
                    [Material] IN VALUES ( 'Detail Only Code'[Material Code] )),
                [Customer])))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexisOlson]

You can write a measure like this to get the $180.

CALCULATE (
    SUM ( Sales[Net Revenue] ),
    Sales[Material] IN VALUES ( Detailing[Material Code] )
)

However, a better solution would probably be to have a dimension table that has all of the material codes and an extra column that categorized them into whatever categories you want (where 101, 102, 200 are all in the "Detailing" category). If you have this dimension table related to your sales table, then you don't need to write measures to do the filtering; you can add a slicer on the category and select whichever ones you want.