cancel
Showing results for
Did you mean:
New Member

## how to use vlookup in power bi

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?

Many thanks 🙏

1 ACCEPTED SOLUTION
Super User II

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

Proud to be a Super User!

5 REPLIES 5
Super User I

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

Proud to be a Super User!

New Member

@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 🙂

Super User II

pls try this

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

Proud to be a Super User!

New Member

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%

Super User II

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

Proud to be a Super User!

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors