Measure to count how many layer under each leader

[Anonymous]

Hi,

I have a summary table that I built in Excel that is counting how many employees under each management level/span of control/layer. The formula I used in excel was like below:

Formula = countif( count how many blank for each selected leader) 

The summary table as below:

Leaders	Management Chain 3	Management Chain 4	Management Chain 5	Management Chain 6	Management Chain 7	Management Chain 8
Leader 1
Leader 2
Leader 3
Leader 4
Leader 5
Leader 6
Leader 7
Leader 8
Leader 9
Leader 10
Leader 11
Leader 12

The data structure is as below, which every chain has managers/employees and what the formula that I have in excel does, is count how many blanks under each management chain for the selected leaders from Management Chain : so if I want to count how many blanks for Leader 1 under Management Chain 3 

Management Chain 2	Management Chain 3	Management Chain 4	Management Chain 5	Management Chain 6	Management Chain 7	Management Chain 8
Leader 1
Leader 2
Leader 3
Leader 4
Leader 5
Leader 6
Leader 7
Leader 8
Leader 9
Leader 10
Leader 11
Leader 12

Is there any recommendation for a DAX measure that can be the same as excel formula?

Thank you!

[Anonymous]

Hi  @Anonymous ,

I created some data:

Here are the steps you can follow：

1. Select the marked column – Replace value

Replace null values with 0, where null values are displayed as null in power bi

2. Select the marked column – Transform – Unpivot Columns.

Result:

3. Create measure.

Measure =
COUNTX(FILTER(ALL('Table'),'Table'[Management Chain 2]=MAX('Table'[Management Chain 2])&&'Table'[Attribute]=MAX('Table'[Attribute])&&'Table'[Value]=0),[Value])

4. Result:

Please click here for the pbix file

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Hi @Anonymous 

Thank you for your help. Just wanted to add more details before I go ahead and try your suggested solution/measure, the data I have are coming through SQL and I don't want to mess the data, if I want to go ahead with this solution, will this impact the data? 

Management Chains are fields within the Headcount Table that I usually get through SQL with specific fields. Please advise?

Thank you,

Zina