Convert Text from a measure into an Integer

[krampit]

Hi all,

I would like to calculate the Total Wage of my employees.
@Harsh and @jafernandezpuga already helped me with calculating the Regular/Evening/Night-hours per shift.
However, the result from the “Regular/Evening/Night-hours measures” is in Text-format. Before I can start calculating the wages (with corresponding Wage-multipliers), I need to convert the Text-format to an Integer.

Usually I would do that with VALUE-function, but I can’t get it work (probably as the Text-format is coming from a measure instead of a column in my dataset)

If someone knows a solution, that would be amazing! Then all my Power BI-problems
are solved.

Mock-up result

For Employee 1:
9 Regular hours * €14 Wage * 1,00 Multiplier = € 126 Total salary

For Employee 2"
( 1 Regular hour * €15 Wage * 1,00 Multiplier ) + (5 Evening hours * €15 Wage *1,10 Multiplier) = € 15 + €82,5 = €97,5

Greetings from Austria.
Preventief v0.1.pbix (114.8 KB)

[Harsh]

Hello @krampit,

Thank You for posting your query onto the Forum.

In order to have the hours based on a “Numerical” format. Below are the measures provided for the reference -

1. Regular Hours - Harsh - Conversion = 
VAR _Regular_Hours = 
IF(

    SELECTEDVALUE( Sales[Date Time Start] ) < SELECTEDVALUE( Sales[Date Time End] ) ,

    IF(

        SELECTEDVALUE( Sales[Date Time Start] ) >= TIME( 8 , 0 , 0 ) &&
        SELECTEDVALUE( Sales[Date Time End] ) <= TIME( 18 , 0 , 0 ) , 

        ROUNDUP(
            DIVIDE(
                DATEDIFF(
                    SELECTEDVALUE( Sales[Date] ) + SELECTEDVALUE( Sales[Date Time Start] ) , 
                    SELECTEDVALUE( Sales[Date] ) + SELECTEDVALUE( Sales[Date Time End] ) , 
                    MINUTE ) ,
                60 , 
                0 ) ,
            2
        ) ,

        IF(

            SELECTEDVALUE( Sales[Date Time Start] ) >= TIME( 8 , 0 , 0 ) &&
            SELECTEDVALUE( Sales[Date Time Start] ) <= TIME( 18 , 0 , 0 ) , 

            ROUNDUP(
                DIVIDE(
                    DATEDIFF(
                        SELECTEDVALUE( Sales[Date] ) + SELECTEDVALUE( Sales[Date Time Start] ) , 
                        SELECTEDVALUE( Sales[Date] ) + TIME( 18 , 0 , 0 ) , 
                        MINUTE ) , 
                    60 , 
                    0 ) , 
                2
            ) ,

            IF(

                SELECTEDVALUE( Sales[Date Time End] ) >= TIME( 8 , 0 , 0 ) &&
                SELECTEDVALUE( Sales[Date Time End] ) <= TIME( 18 , 0 , 0 ) , 

                ROUNDUP(
                    DIVIDE(
                        DATEDIFF(
                            SELECTEDVALUE( Sales[Date] ) + TIME( 8 , 0 , 0 ) ,
                            SELECTEDVALUE( Sales[Date] ) + SELECTEDVALUE( Sales[Date Time End] ) , 
                            MINUTE ) , 
                        60 , 
                        0 ) , 
                    2
                )
            )
        )
    )
)

VAR _Results = 
IF(

    _Regular_Hours < 0 , 

    _Regular_Hours * -1 , 

    _Regular_Hours )


RETURN
_Results

3. Evening Hours - Harsh - Conversion = 
VAR _Evening_Hours =
IF(
    
    SELECTEDVALUE( Sales[Date Time Start] ) >= TIME( 18 , 0 , 0 ) &&
    SELECTEDVALUE( Sales[Date Time Start] ) <= ( TIME( 23 , 59 , 59 ) + TIME( 0 , 0 , 1 ) ) ,

    ROUNDUP(
        DIVIDE(
            DATEDIFF(
                SELECTEDVALUE( Sales[Date] ) + SELECTEDVALUE( Sales[Date Time Start] ) ,
                SELECTEDVALUE( Sales[Date] ) + ( TIME( 23 , 59 , 59 ) + TIME( 0 , 0 , 1 ) ) , 
                MINUTE ) , 
            60 , 
            0 ) ,
        2
    ) ,

    IF(

        SELECTEDVALUE( Sales[Date Time End] ) >= TIME( 18 , 0 , 0 ) &&
        SELECTEDVALUE( Sales[Date Time End] ) <= ( TIME( 23 , 59 , 59 ) + TIME( 0 , 0 , 1 ) ) ,

        ROUNDUP(
            DIVIDE(
                DATEDIFF(
                    SELECTEDVALUE( Sales[Date] ) + SELECTEDVALUE( Sales[Date Time End] ) ,
                    SELECTEDVALUE( Sales[Date] ) + TIME( 18 , 0 , 0 ) , 
                    MINUTE ) , 
                60 , 
                0 ) , 
            2
        )
    )
)

VAR _Results =
IF(
    
    _Evening_Hours < 0 , 

    _Evening_Hours * -1 , 

    _Evening_Hours )


RETURN
_Results

5. Night Hours - Harsh - Conversion = 
VAR _Night_Hours = 
IF(
    
    SELECTEDVALUE( Sales[Date Time Start] ) >= TIME( 0 , 0 , 0 ) &&
    SELECTEDVALUE( Sales[Date Time Start] ) < TIME( 8 , 0 , 0 ) ,

    ROUNDUP(
        DIVIDE(
            DATEDIFF(
                SELECTEDVALUE( Sales[Date] ) + SELECTEDVALUE( Sales[Date Time Start] ) ,
                SELECTEDVALUE( Sales[Date] ) + TIME( 8 , 0 , 0 ) , 
                MINUTE ) , 
            60 , 
            0 ) ,
        2
    ) , 

    IF(

        SELECTEDVALUE( Sales[Date Time End] ) >= TIME( 0 , 0 , 0 ) &&
        SELECTEDVALUE( Sales[Date Time End] ) < TIME( 8 , 0 , 0 ) ,

        ROUNDUP(
            DIVIDE(
                DATEDIFF(
                    SELECTEDVALUE( Sales[Date] ) + SELECTEDVALUE( Sales[Date Time End] ) ,
                    SELECTEDVALUE( Sales[Date] ) + TIME( 0 , 0 , 0 ) , 
                    MINUTE ) , 
                60 , 
                0 ) , 
            2
        )
    )
)

VAR _Results = 
IF(

    _Night_Hours < 0 , 

    _Night_Hours * -1 , 

    _Night_Hours )


RETURN
_Results

7. Total Hours - Harsh - Conversion = 
IF( 
    SELECTEDVALUE( Sales[Date Time End] ) > SELECTEDVALUE( Sales[Date Time Start] ) , 

    DIVIDE(
        DATEDIFF(
            SELECTEDVALUE( Sales[Date] ) + SELECTEDVALUE( Sales[Date Time Start] ) , 
            SELECTEDVALUE( Sales[Date] ) + SELECTEDVALUE( Sales[Date Time End] ) , 
            MINUTE ) ,
        60 , 
        0 ) ,

    ROUNDUP(
        DIVIDE(
            DATEDIFF(
                SELECTEDVALUE( Sales[Date] ) + SELECTEDVALUE( Sales[Date Time Start] ) , 
                SELECTEDVALUE( Sales[Date] ) + TIME( 23 , 59 , 59 ) + TIME( 0 , 0 , 1 ) , 
                MINUTE ) +

            DATEDIFF(
                SELECTEDVALUE( Sales[Date] ) + TIME( 0 , 0 , 0 ) , 
                SELECTEDVALUE( Sales[Date] ) + SELECTEDVALUE( Sales[Date Time End] ) , 
                MINUTE ) ,
            60 , 
            0 
        ) ,
    0 )
)

Once the “Main Measures” are written, write the small measures for each of them to get the “Grand Totals” as well. Below are the measures provided for the reference -

2. Regular Hours - Harsh - Conversion - Totals = 
SUMX(
    SUMMARIZE(
        Sales , 
        Sales[Date] , 
        Sales[Employee ID] , 
        Sales[Date Time Start] , 
        Sales[Date Time End] , 
        "@Totals" , 
        [1. Regular Hours - Harsh - Conversion] ) , 
    [@Totals]
)

4. Evening Hours - Harsh - Conversion - Totals = 
SUMX(
    SUMMARIZE(
        Sales , 
        Sales[Date] , 
        Sales[Employee ID] , 
        Sales[Date Time Start] , 
        Sales[Date Time End] , 
        "@Totals" , 
        [3. Evening Hours - Harsh - Conversion] ) , 
    [@Totals]
)

6. Night Hours - Harsh - Conversion - Totals = 
SUMX(
    SUMMARIZE(
        Sales , 
        Sales[Date] , 
        Sales[Employee ID] , 
        Sales[Date Time Start] , 
        Sales[Date Time End] , 
        "@Totals" , 
        [5. Night Hours - Harsh - Conversion] ) , 
    [@Totals]
)

8. Total Hours - Harsh - Conversion - Totals = 
SUMX(
    SUMMARIZE(
        Sales , 
        Sales[Date] , 
        Sales[Employee ID] , 
        Sales[Date Time Start] , 
        Sales[Date Time End] , 
        "@Totals" , 
        [7. Total Hours - Harsh - Conversion] ) ,
    [@Totals]
)

After writing all these measures, you’ll be able to see the desired results. Below is the screenshot of the final results provided for the reference -

I’m also attaching the working of the PBIX file as well as providing a link below for the reference purposes pertaining to the topic - “How to Fix Incorrect Totals” which was created by our expert @Greg.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Shift Hours - Numerical Format - Harsh.pbix (30.5 KB)

Fix Incorrect Totals

When using a measure column in a visual, it is not uncommon in Power BI to find that the totals are incorrect. The Fix Incorrect Totals DAX pattern can be used to correct this issue. When a DAX measure is providing the correct detail value it often, unfortunately, provides an incorrect value for the total row. This is due to there being no evaluation context for the total row. For example, when comparing the [Total Sales] to the [Sales LY] to find the minimum for a customer using the simple DAX…

[krampit]

Ladies and Gentleman,

May I have a standing ovation for @Harsh!
Incredible, thanks a lot. This makes me very motivated to keep practicing with Power BI.

[Harsh]

Hello @krampit,

Thank You so much for your kind words.

I’m glad that I was able to assist you.

Thanks and Warm Regards,
Harsh