locked
Dax Calculating ratios based on multiple conditions RRS feed

  • Question

  • I am new to DAX and need some help to understand if there is a better way to achieve the results I am expecting to get.

    Requirement:

    Calculate compensation ratio per staff member. Compensation Ratio formula varies based on Compensation frequency M for monthly and H for hourly.

    Compa Ratio  (Monthly) = (Compensation Annual Rate])/2080)/ Sal Grade Midpoint Hrly Rate

    Compa Ratio (Hourly) = Compensation Rate/Sal Grade Midpoint Hrly Rate

    Now a Staff member can have multiple salary records for a fiscal year (July-June). If the staff member has a current record (indicated by field Cur-Hist-Fut Indicator) then this is the record were the calculation occurs. However, the staff member may have terminated in the current fiscal year and we need the most current historic record for the calculation.

    Currently, I have built two separate formulas for Current year and Prior year. The current year formula does not account for people terminated in the current year and returns blank. I need to add the condition to get the most recent historic record. I feel the formulas have a lot of repeated and redundant conditions built in.

    It would be very helpful if someone could look at the formulas below and guide me to a more efficient or better way of doing this. Any suggestions regarding alternatives are greatly appreciated!

    /* For Current Year records

    Compa-Ratio CY :=

    IF (

        CALCULATE (

            COUNTROWS ( 'Job' ),                                      

            Job[Cur-Hist-Fut Indicator] = "C",

            Job[Compensation Frequency Code] = "M"

        )

            = 1,

        CALCULATE (

            ( SUM ( [Compensation Annual Rate] ) / 2080 )

                / SUM ( [Sal Grade Midpoint Hrly Rate] ),

            Job[Compensation Frequency Code] = "M"

        ),

        CALCULATE (

            ( SUM ( [Compensation Rate] ) ) / SUM ( [Sal Grade Midpoint Hrly Rate] ),

            Job[Cur-Hist-Fut Indicator] = "C",

            Job[Compensation Frequency Code] = "H"

        )

    )

    /* For Prior Year records

    Compa-Ratio PY :=

    IF (

        CALCULATE (

            COUNTROWS ( 'Job' ),

            Job[Cur-Hist-Fut Indicator] = "H",

            Job[Compensation Frequency Code] = "M",

            FILTER (

                'Job',

                AND (

                    Job[Cur-Hist-Fut Indicator] = "H",

                    Job[Job Row Effective Date] = MAX ( Job[Job Row Effective Date] )

                )

            )

        )

            = 1,

        CALCULATE (

            ( SUM ( [Compensation Annual Rate] ) / 2080 )

                / SUM ( [Sal Grade Midpoint Hrly Rate] ),

            Job[Cur-Hist-Fut Indicator] = "H",

            Job[Compensation Frequency Code] = "M",

            FILTER (

                'Job',

                AND (

                    Job[Cur-Hist-Fut Indicator] = "H",

                    Job[Job Row Effective Date] = MAX ( Job[Job Row Effective Date] )

                )

            )

        ),

        CALCULATE (

            ( SUM ( [Compensation Rate] ) ) / SUM ( [Sal Grade Midpoint Hrly Rate] ),

            Job[Cur-Hist-Fut Indicator] = "H",

            Job[Compensation Frequency Code] = "H",

            FILTER (

                'Job',

                AND (

                    Job[Cur-Hist-Fut Indicator] = "H",

                    Job[Job Row Effective Date] = MAX ( Job[Job Row Effective Date] )

                )

            )

        )

    )

    Thank you!


    • Edited by divkor Tuesday, March 1, 2016 8:57 PM Data Snapshot added
    Tuesday, March 1, 2016 5:04 PM

Answers

All replies