Remove blanks without changing the measure value

[Bratone]

Hello,

I have the following situation. I want to calculate the % of leaves for a specific team and its members. The measure that I used is very basic:

%ofLeaves = [NrOfLeaves] / [AvailableResources]

The problem is that the value changes at manager/overall level if for example I remove the blanks on the column NrOfLeaves.

Can you please tell me what should I add in the formula to keep the original % values, both on a employee level and manager level ?

Thank you!

[Anonymous]

Hi @Bratone ,

Please try below steps:

1. below is my test table

2. create two meaure with below dax formula

Measure =
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        'Table'[Manager] = SELECTEDVALUE ( 'Table'[Manager] )
    )
VAR sum_nr =
    SUMX ( tmp, [NrOfLeaves] )
VAR sum_ab =
    SUMX ( tmp, [Availables] )
RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( 'Table'[Sub_Mg] ),
            DIVIDE (
                SELECTEDVALUE ( 'Table'[NrOfLeaves] ),
                SELECTEDVALUE ( 'Table'[Availables] )
            ),
        ISINSCOPE ( 'Table'[Manager] ), DIVIDE ( sum_nr, sum_ab ),
        DIVIDE (
            SUMX ( ALL ( 'Table' ), [NrOfLeaves] ),
            SUMX ( ALL ( 'Table' ), [Availables] )
        )
    )

Measure2 =
VAR tmp =
    CALCULATETABLE ( VALUES ( 'Table'[Sub_Mg] ), 'Table'[NrOfLeaves] = BLANK () )
VAR cur_submg =
    SELECTEDVALUE ( 'Table'[Sub_Mg] )
RETURN
    IF ( cur_submg IN tmp, 0, 1 )

3. add a matrix visual, then add fields and "Measure" to it, add "Measure 2" to the "Filters" pane

add measure to "Filter" pane is used to don't show the blank data.

Befor add:

After add:

Please refer the attached .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Bratone]

Hello,

Thank you for taking the time to reply, the problem is that in my case, NrOfLeaves and AvailableResources are actually measures, not columns in the same raw data tabel:

NrOfLeaves = CALCULATE(COUNTA(Schedule_Manager[Leave Category]),Schedule_Manager[Leave Category]<>BLANK()) ==> Basically this counts the number of Leave Category reasons for every individual, to see how many leave days they had

AvailableResources = (CALCULATE(COUNTA(Employeess[Login Key]),Employeess[Attrition]="No"))*[BusinessDays] ==> Basically this checks if the individual is an active employee and if yes his "score" for AvailableResource is multiplied with the number of BusinessDays in that week/month etc.

So any idea on how can I adapt the formula to take into account the measures and not the columns ?

Thanks again!

[Anonymous]

Hi @Bratone ,

Since I don't know what your actual environment is like, I couldn't give you an exact answer, but for measure you could also use similar operations to columns, and I think my example is a good reference for you. Please try to adjust it by yourself.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.