% of Total

[stbrooks13]

This sounds so easy!

I have a table containing my number of hired interns and associates by year and by school. Then I have a table containing how much we paid each school by year.

I'm trying to come up with a DAX formula that takes the total paid to the school and divide it by the % of total interns and associates on the PowerPivot table. But, the total amount paid to the school is showing in both the intern and associate columns.

For instance, if we paid $100 to a school, and hired 7 interns and 3 associates, I want $70 to show as going towards the interns and $30 to show as going towards the interns.

 

[Comfy]

You've not had a reply so thought I would post (I'm super new to this and I'm positive someone could post a proper measure!!)

I can only get this to work by including a count of Interns and Associates in the Pivot.

Assuming the following Tables:

dTypes

Excel 2010
	A	B	C	D
1	Type	School	Year
2	Intern	A	2015
3	Associate	A	2015
4	Intern	A	2015
5	Associate	A	2015
6	Intern	A	2015
7	Intern	B	2015
8	Intern	B	2015
9	Intern	B	2015
10	Associate	B	2015
11	Intern	A	2016
12	Associate	A	2016
13	Intern	A	2016
14	Associate	B	2016
15	Intern	B	2016
16	Intern	B	2016
17	Intern	B	2016
18	Intern	B	2016
19	Associate	B	2016
Sheet1

tFact

Excel 2010
	G	H	I	J
1	School	Year	Paid
2	A	2015	125
3	B	2015	200
4	A	2016	135
5	B	2016	240
Sheet1

dSchools

Excel 2010
	E
2	Schools
3	A
4	B
5
Sheet1

dYears

Excel 2010
	E
7	Years
8	2015
9	2016
10
Sheet1

Measure in dTypes
CountTypes:=COUNTA([Type])

Measure in tFact
TotalPaid:=SUM(tFact[Paid])

 

[Comfy]

Actually Ignore that

 

[Comfy]

Remove 'CountTypes' from the Pivot and update the TotalPaid Measure to:

=SUM(tFact[Paid])*(dTypes[CountTypes]/CALCULATE(dTypes[CountTypes], All(dTypes[Type])))

This appears to work.

Excel 2010
	B	C	D	E	F
3	TotalPaid
4	Schools	Years	Type	Total
5	A	2015	Associate	£50.00
6			Intern	£75.00
7		2015 Total		£125.00
8		2016	Associate	£45.00
9			Intern	£90.00
10		2016 Total		£135.00
11	A Total			£260.00
12	B	2015	Associate	£50.00
13			Intern	£150.00
14		2015 Total		£200.00
15		2016	Associate	£80.00
16			Intern	£160.00
17		2016 Total		£240.00
18	B Total			£440.00
19	Grand Total			£700.00
20
Sheet4