Summary Data at Different Level

Anything related to PowerPivot and DAX Formuale
g4mb1t
Posts: 5
Joined: Sun Jun 26, 2016 11:41 am

Summary Data at Different Level

Postby g4mb1t » Sun May 14, 2017 9:23 pm

Hi,

I have an issue involving data at different granularity level.

Lookup Table:
a. School
b. Department (Each department belongs to a School)

Data Table:
a. Staff data (data available only at Department level):
- Staff Type: Academic or Admin
- Staff No

b. Space data (data available only at School level)
- Space Type: Lab or General
- Space Area

I would like to have a table that summarises these data. So I would like the table to be:

Faculty Name, School Name, Dept Name, %Academic Staff in Dept, %Academic Staff in School, %Academic Staff in Faculty, %Lab Area in School, %Lab Area in Faculty

I have attached the file to illustrate the output.

Thank you for the help.
Attachments
PowerPivot.xlsx
(290.21 KiB) Downloaded 12 times

PhilC
Posts: 145
Joined: Tue Sep 09, 2014 8:13 am

Re: Summary Data at Different Level

Postby PhilC » Mon May 15, 2017 11:00 am

Hi g4mb1t,

Can you describe the issue you are having with the granularity? What have you tried so far in terms of getting the results you are after?

The model looks to be set up well. One option to consider might be to combine the Faculty and Dept tables into one table (you could do this via Power Query upon import, or in the source of this information).

You will probably need to build up your measures using ALL(field) to remove the row context based on the output required (ie for the % School, have ALL(Dept), and for % Faculty, have ALL(School & Dept). DIVIDE will be your friend.

Maybe try creating the measures yourself and post back with specific questions on the issue you are having.

Cheers
Phil

g4mb1t
Posts: 5
Joined: Sun Jun 26, 2016 11:41 am

Re: Summary Data at Different Level

Postby g4mb1t » Mon May 15, 2017 7:19 pm

Hi PhilC,

The reason why I cannot combine both Faculty and Dept tables together is because the staff data is available on dept level whereas the space data is available on school level (which is 1 level up). This is what I meant by data at different granularity (I am unsure of the correct term).

The result I am after is:
Faculty Name, School Name, Dept Name, %Academic in Dept, %Academic in School, %Academic in Faculty, %Lab area in School, %Lab Area in Faculty

I realize that all department belonging to the same School will have a similar value in %Academic in School. The same goes with all schools belonging to the same faculty will have the same value in %Academic in FAculty.

I've updated the file and I've created several measures:
1. Total Staff:=sum(StaffData[Staff No])
2. Total Academic:=CALCULATE([Total Staff],StaffData[Staff Type]="Academic")
3. %Academic in Dept:=[Total Academic]/[Total Staff]

It is the %Academic in School that I am having problem with because of the disjointed structure of the two tables (Faculty & Dept).

I've tried using %Academic in School := CALCULATE([Total Staff], ALL(DeptStructure),DeptStructure[SchoolCode]=FacultyStructure[SchoolCode]) but it is giving me an error.

Thank you
Attachments
PowerPivot.xlsx
(313.56 KiB) Downloaded 13 times

PhilC
Posts: 145
Joined: Tue Sep 09, 2014 8:13 am

Re: Summary Data at Different Level

Postby PhilC » Tue May 16, 2017 12:28 pm

Thanks for the additional information and having a go at the measures.

I have not been able to create a solution but the attached might help you along. The Academic measures are working as expected (note, some of your expected result calcs are not correct), but when I start to add in Space info it is not working.

I combined the Faculty and Dept Org tables and loaded via PQ. I added a few helper measures to test the ALL filters, so not everything I added is needed, but helped me with visibility of the calcs. The ALL filters need to be based on the fields used in the Pivot itself, so if these might change you might want to expand what is included or use ALLEXCEPT.

Hopefully someone with more experience might be able to help with a working approach.

Cheers
Phil
Attachments
PPF Granularity Question.xlsx
(419.34 KiB) Downloaded 11 times


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 3 guests