BASIC REPORT

Report

Power BI

Power Bi

I was able to:

• Analyse the data that i got from DYNAMO
• Create measures and calculated columns corresponding to my needs
• organize and clean the data into a data model
• create visualizations of the data using graphs and tables

Retrieving Data

Dynamo

= Json.Document(Web.Contents("https://api.dynamosoftware.com/api/v2.1/Entity/Transaction?all=true&mobile=false&next=0", [Headers=[#"x-columns"="Financing round, Transaction Type, Effective date, Investing entity, Investment in, Amount, Investor Account Investment in, Investor Account Investing entity,Leveraged Capital (USAID),IM Multiplier,Ownership %", Authorization="FibLNhdrmFKRMtlxykPK4QhyMXVLgA+LqNk/2q1oI+puLKmGL2Bd0D7w0N0B6vna7421UwsxqHZX9+XMu2yV2E4COZYY1uj8PKLvOA41a3oqBpBPTyhX54srZyoBCTUI.X6tkXL+xrnCSiZObRZK0jd+ucqwVvCoVMiyTRHBJ1WauxYYQjzcaQRwPSuxf/kLfVkkYD9huee23aCpYO7wmMQ=="]]))

Intelligent Period

Automated Time

EndDatePreviousFY = EDATE(IntelligentPeriod[EndDateCurrentFY], -12)

EndDateCurrentFY = DATE(IF(OR(OR(MONTH(TODAY())=10,MONTH(TODAY())=11),MONTH(TODAY())=12),YEAR(TODAY()), YEAR(TODAY())-1),09,30)

StartDateCurrentFY = VAR yearInQuestion = YEAR([EndDateCurrentFY])-1 return DATE(yearInQuestion, 10, 1)

StartDatePreviousFY = IF( YEAR(TODAY())-3=2020,DATE(2020,11,30),EDATE([StartDateCurrentFY], -12) )

Process

How i approached the indicators

Female Participant

In order to get the Female Participant:

• Names of participants who have activities
• Names of the investors (Investor accounts)
• Exclude Exited Companies
• Filter the participants by their gender
• Filter the participants end date and start date (employee)

25%

Female Participant

VIEW

Female Participant

contact names in activity i= var a=LOOKUPVALUE(Activity[Contacts.1],Activity[Contacts.1],'Contacts'[FullName]) var b=LOOKUPVALUE(Activity[Contacts.2],Activity[Contacts.2],'Contacts'[FullName]) var c=LOOKUPVALUE(Activity[Contacts.3],Activity[Contacts.3],'Contacts'[FullName]) var d=LOOKUPVALUE(Activity[Contacts.4],Activity[Contacts.4],'Contacts'[FullName]) var e=LOOKUPVALUE(Activity[Contacts.5],Activity[Contacts.5],'Contacts'[FullName]) var f=IF(OR(OR(OR(OR('Contacts'[FullName]=a,'Contacts'[FullName]=b),'Contacts'[FullName]=c),'Contacts'[FullName]=d),'Contacts'[FullName]=e),'Contacts'[FullName],BLANK()) return f

1. Names in ACTIVITY table

• LOOKUPVALUE
• IF function
• OR function

Female Participant

2. Names as investing entities

LOOKUPVALUE('Investor Accounts'[Investor Account Name],'Investor Accounts'[Parent Investor],'Contacts'[FullName])

• Bring the account name from the investor account table
• compare the parent name to the contact full name
• retrieve the names where condition is valid

Female Participant

3. Bring the participant employees

var a= LOOKUPVALUE('Deal'[Name],'Deal'[Pipeline status],"Exited") var b=IF('Contacts'[Gender]="X" && 'Contacts'[Company Name]<>a && 'Contacts'[Company Name]<>BLANK(), IF( Contacts[Employment Start Date]<=[EndDateCurrentFY] && NOT(ISBLANK(Contacts[Employment Start Date])) , IF( OR('Contacts'[Employment End Date]>IntelligentPeriod[EndDateCurrentFY],ISBLANK('Contacts'[Employment End Date])), 'Contacts'[FullName])

• Filter companies (excluding existed companies)
• Filter start date
• Filter end date

Female Participant

4. Final Female Participant DAX

Female Participate = var a= LOOKUPVALUE('Deal'[Name],'Deal'[Pipeline status],"Exited") var b=IF('Contacts'[Gender]="X" && 'Contacts'[Company Name]<>a && 'Contacts'[Company Name]<>BLANK(), IF( Contacts[Employment Start Date]<=[EndDateCurrentFY] && NOT(ISBLANK(Contacts[Employment Start Date])) , IF( OR('Contacts'[Employment End Date]>IntelligentPeriod[EndDatePreviousFY] ,ISBLANK('Contacts'[Employment End Date])), 'Contacts'[FullName]), CONCATENATE(Contacts[contact names in activity],Contacts[inv entity indiv])),BLANK()) return b

Female Participant

5. Filter the results according to the needed results

Intervention example

Sector example

AGE example

• Sector
• Intervention
• Age 15-29
• Age 30+

Age 15-29 = var a=CALCULATE( COUNT('Contacts'[Female Participate]),'Contacts'[Age]<30,'Contacts'[Age]>15) var b=CALCULATE( COUNT('Contacts'[Participantant employee]),'Contacts'[Age]<30,'Contacts'[Age]>15) var c=a/b return c

Agriculture Intervention = var a= DIVIDE(CALCULATE(COUNT('Contacts'[Female Participate]),'Contacts'[intervention]="Agriculture",Contacts[Gender]="X"),CALCULATE(COUNT('Contacts'[Participantant employee]),'Contacts'[intervention]="Agriculture")) var b=IF(ISBLANK(a),"-",a) return b

IT count = var a=DIVIDE(CALCULATE(COUNT('Contacts'[Female Participate]),'Contacts'[sector]="IT",Contacts[Gender]="X"),CALCULATE(COUNT('Contacts'[Participantant employee]),Contacts[sector]="IT")) var b=IF(ISBLANK(a),"-",a) return b

Results

Indicator Visualizations

Applied Management Practices

IR-2.1.a

16

beneficiaries who have applied new technologies or management practices

Applied Improved organizational-level technologies

Title here

17,20

MSMEs receiving business development services

IR-2.1.b

Prev

Average price per unit over the period (Domestic)

0.31

IR-2.1.c

Value of sales collected firm-level

Average price per unit over the period (Export)

200.29

Vertical Linkage

IR-2.1.2.a

organizations benefiting from new horizontal and vertical linkages

Title here

Horizontal Linkage

Title here

17,20

10

individuals with improved skills

IR-2.1.3.a

AGE 30+

AGE 15-19

AGE 25-29

AGE 20-24

Title here

17,520

Leverage Capital

DO-2a

New Jobs

DO-2b

Proportion of female participants

DO-2c

IR-2.2.a

enterprises that have successfully accessed loans, private equity, or both

IR-2.2.b

financial intermediaries supported by the Program

The End

Thank you :)

Current

Average price per unit over the period (Domestic)

0.71

IR-2.1.c

Value of sales collected firm-level

Average price per unit over the period (Export)

200.29