Forum Discussion

Bratone's avatar
Bratone
Helper I
3 years ago

Remove blanks without changing the measure value

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!

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      Bratone
      Helper I
      3 years ago

      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's avatar
        Anonymous
        Not applicable
        3 years ago

        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.