Need Help on DAX formula..

[engyin2]

Hi 

I wrote the below measure in BI to have the result of the no. of reporting as shown in excel.  

Span = CALCULATE(COUNT(WD[Workday ID])) / CALCULATE(COUNT(WD[Is Manager]), FILTER(WD, WD[Is Manager] = "Yes"))

 But I am not sure how to 'tell' the BI to point back to the previous layer for the manager HC count. Can anyone help ? Thanks ! 

[afzalphatan]

Hi... 

First you need to change the Layer format to whole number format. 

Below is the dax formula and pic demonstrating the req result .....

Hope you enjoy it..

Span Final:=
Var Numerator = IF(HASONEVALUE(Table1[Layer]), 
                    CALCULATE(COUNTROWS(Table1), FILTER(ALL(Table1), Table1[Layer] = VALUES(Table1[Layer]) + 1)))
Var Denominator = CALCULATE(COUNTROWS(Table1), Table1[Is Manager] = "Yes")

Return 
DIVIDE(Numerator, Denominator)

[afzalphatan]

Can you elaborate more on the base data...

where is coulumn header "Is manager" and "workday id"

[engyin2]

Hi , 

WD id is the employee id. I am using it to count the HC by each department and job layer. 

And out of each department, I am identified if particular HC is a manger or not , hence column "is manager" will have "yes" or "no".

I would like to create the measure to see the no. of HC by department reporting to the manager in the previous layer but encountered problem to have the measure written. 

Appreciate your help and thanks.

[Ashish_Mathur]

Hi,

Share your source dataset and show the expected result.

[engyin2]

Hi Ahish, 

Here is the example of the source and the expected result. Thanks.

[Ashish_Mathur]

Hi,

I cannot see the column headings - what is O9? N8?

[engyin2]

[Zubair_Muhammad]

@engyin2

Try this MEASURE

Span =
CALCULATE ( COUNT ( WD[Workday ID] ) )
    / CALCULATE (
        COUNT ( WD[Is Manager] ),
        FILTER (
            ALL ( WD ),
            WD[Is Manager] = "Yes"
                && WD[Layer]
                    = SELECTEDVALUE ( WD[Layer] ) - 1
        )
    )

[engyin2]

Hi, 

Thanks for your help. But it dun really work. Any idea ? I have the below message prompted :

[v-xjiin-msft · Accepted solution]

Hi @engyin2,

First your error, it is because that the Column Layer is a Text type column. It cannot be used to minus 1 directly. To resolve this error, you just need to change the column typle to whole number.

Then to achieve your requirement, you can try following measure:

Span =
CALCULATE (
    COUNT ( WD[Layer] ),
    FILTER ( ALL ( WD ), WD[Layer] = SELECTEDVALUE ( WD[Layer] ) + 1 )
)
    / CALCULATE (
        COUNT ( WD[Layer] ),
        FILTER ( WD, WD[Is Manager] = "Yes" ),
        ALLEXCEPT ( WD, WD[Layer] )
    )

Thanks,
Xi Jin.