• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Nationalization

Mohammed Ashraf

New Member
Hello Super Team,

I need your support again please.

I need a query in powerbi to create a measure or a column to compare the country vs the nationality to get the nationalization %

Please let me know if you need more details.

Many Many Thanks,

Ashraf
 

Chihiro

Excel Ninja
First, I'd double check your data. Some Nationality is misspelled/wrong.
Ex: Bharaini should be Bahraini, Saudi Arabian should be Saudi. etc.

At any rate, you would start off with creating translation table for Country and what corresponding nationality would be.

CountryPeople
AlgeriaAlgerian
BahrainBahraini
EgyptEgyptian
IranIranian
IraqIraqi
JordanJordanian
KuwaitKuwaiti
LebanonLebanese
MoroccoMoroccan
OmanOmani
PalestinePalestinian
QatarQatari
Saudi ArabiaSaudi
SyriaSyrian
TunisiaTunisian
United Arab EmiratesEmirati
YemenYemeni

Then load both into PQ, merge (Left Outer Join) using Nationality as key column. Using Employee table as left table. Expand "Country" from translation table.

Add custom column and name it "National" (True/False).
=[Employee Country] = [Country]

Load both tables to data model. Create relationship from Translation to Employee on Country column.

Add following measures.
Employee Count:=COUNTROWS(Emplyee)
Nationals:=CALCULATE(COUNTROWS(Emplyee),FILTER(Emplyee,Emplyee[National]=TRUE()))
Expats:=[Employee Count]-[Nationals]
Nationalization:=DIVIDE([Nationals],[Employee Count],0)

Add fields and measures to Pivot table.

See attached sample.
 

Attachments

Chihiro

Excel Ninja
Not Power BI. Power Query (also called Get & Transform). It's in the data tab. I assumed you had it, since you posted in Power tools section of the forum.
 
Top