how to use vlookup in power bi

[jjmao]

Hello everyone

I have a sale data and in excel, i use vlookup to calculate the percent of member type who had purchased.

Is it possible to do the same in power bi? I am stuck, can anyone advice? 

https://drive.google.com/file/d/1JQhCMuJObYubhM15_NyIltrGlIt4l9LS/view?usp=sharing

Many thanks 🙏

[vanessafvg]

you can create a calc like this

% Member Type 2 =

var totalsales = CALCULATE(sum(Sales[Amt spend]), all(Legend))

var salesamt = CALCULATE(sum(Sales[Amt spend]))

var ratio = DIVIDE(salesamt, totalsales,0)

return ratio

[jjmao]

@vanessafvg 
actually i dont need the sales amount.

i trying to find out for the april sales, example.

In the sale data, there are 9 VIP who make purchase. 

In the member database, there are total of 26 VIP

So total percent who VIP who made purchase =35%

Is that possible? thank you very much 🙂

[ryan_mayu]

@jjmao 

pls try this

Measure = distinctcount(Sales[MembershipID])/COUNTROWS('Member Database')

[jjmao]

@ryan_mayu 

hello.

I tried the formula but the percent is different when done in excel?

for example: 

9 VIP made purchase & there are total of 26 VIP

So total percent made purchase =35%

[ryan_mayu · Accepted solution]

@jjmao 

i created a column in sales table

Column = LOOKUPVALUE('Member Database'[Membership Code],'Member Database'[MembershipID],Sales[MembershipID])

there are 10 records and two duplicated members. So the result for distinctcount is 8