Calculate percentage over time from two tables

[Anonymous]

Hi, I have two tables in power BI that look like

projects

Project	Category
A	C1
B	C2
C	C1
D	C2
E	C2

project_years

Project	Year
A	2015
B	2015
B	2016
C	2016
D	2016
E	2016

they are connected by the Project column

I want to calculate the % of projects in each category over time e.g.

Year	Category	Percentage
2015	C1	50
2015	C2	50
2016	C1	25
2016	C2	75

which in SQL would be

with projects as (
  select 'A' as project, 'C1' as category
  union
  select 'B' as project, 'C2' as category
  union
  select 'C' as project, 'C1' as category
  union
  select 'D' as project, 'C2' as category
  union
  select 'E' as project, 'C2' as category
), project_years as (
  select 'A' as project, 2015 as year
  union
  select 'B' as project, 2015 as year
  union
  select 'B' as project, 2016 as year
  union
  select 'C' as project, 2016 as year
  union
  select 'D' as project, 2016 as year
  union
  select 'E' as project, 2016 as year
)
select 
  year,
  category,
  100 * count(*)/sum(count(*)) over (partition by year) 
from 
  projects p
  inner join project_years y on y.project = p.project
group by category, year

I'm not sure how to do this in power BI because they're in separate tables - is there a way to do the join and counting in a measure or do they need to be joined in a query and then a measure done on that?

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please check the below picture and the sample pbix file's link down below.

Percentage =
DIVIDE (
CALCULATE (
COUNTROWS ( Projects ),
CROSSFILTER ( 'Project Years'[Project], Projects[Project], BOTH )
),
CALCULATE (
COUNTROWS ( Projects ),
ALLSELECTED ( Projects[Category] ),
CROSSFILTER ( 'Project Years'[Project], Projects[Project], BOTH )
)
)

https://www.dropbox.com/s/dpdu5n15pnrnp97/richard.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

Fantastic, thank you for the help