Nationalization

[Mohammed Ashraf]

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]

Without sample workbook with data set and mockup of desired result, bit hard to help.

 

[Mohammed Ashraf]

Hello Chihiro,

I understand. Attaching a sample file & the output which i would like to have for all the countries. Please let me know if you need anything else.

Cheers!
Ashraf

 

[Chihiro]

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.

Country	People
Algeria	Algerian
Bahrain	Bahraini
Egypt	Egyptian
Iran	Iranian
Iraq	Iraqi
Jordan	Jordanian
Kuwait	Kuwaiti
Lebanon	Lebanese
Morocco	Moroccan
Oman	Omani
Palestine	Palestinian
Qatar	Qatari
Saudi Arabia	Saudi
Syria	Syrian
Tunisia	Tunisian
United Arab Emirates	Emirati
Yemen	Yemeni

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.

 

[Mohammed Ashraf]

Thank you. How do i use this in power bi? Sorry i am very new to power bi. Apologize of my question is silly.

 

[Chihiro]

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.