Forum Discussion

dfs29's avatar
dfs29
Helper I
3 years ago
Solved

divide function

Hello ,    I am trying to divide "_Total (hm)" with "_Total_total" but it dosn't work because total type is text not number  So I tried to convert "_Total (hm)" to second and try again but failed ...
General Comment
Need Help
Show and Tell
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    Hi dfs29 ,

     

    If they are text type, please try:

    hm = 
var _hh = VALUE(LEFT(MAX('Table'[_Total (hm)]),(FIND(":",MAX('Table'[_Total (hm)]))-1)))
var _mm = VALUE(LEFT(RIGHT(MAX('Table'[_Total (hm)]),5),2))
var _ss = VALUE(RIGHT(MAX('Table'[_Total (hm)]),2))
return _hh*60*60+_mm*60+_ss

total = 
var _hh = VALUE(LEFT(MAX('Table'[_Total_total]),(FIND(":",MAX('Table'[_Total_total]))-1)))
var _mm = VALUE(LEFT(RIGHT(MAX('Table'[_Total_total]),5),2))
var _ss = VALUE(RIGHT(MAX('Table'[_Total_total]),2))
return _hh*60*60+_mm*60+_ss

Percentage = DIVIDE([hm],[total])

    Final output:

     

    Best Regards,

    Jianbo Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-jianboli-msft's avatar
v-jianboli-msft
Community Support
3 years ago

Hi dfs29 ,

 

This is caused by the context of the meaure.

I have modified the measure, please try:

 

Percentage =
VAR _a =
    ADDCOLUMNS (
        'Table',
        "hm",
            VALUE (
                LEFT ( 'Table'[_Total (hm)], ( FIND ( ":", 'Table'[_Total (hm)] ) - 1 ) )
            ) * 60 * 60
                + VALUE ( LEFT ( RIGHT ( 'Table'[_Total (hm)], 5 ), 2 ) ) * 60
                + VALUE ( RIGHT ( 'Table'[_Total (hm)], 2 ) ),
        "total",
            VALUE (
                LEFT ( 'Table'[_Total_total], ( FIND ( ":", 'Table'[_Total_total] ) - 1 ) )
            ) * 60 * 60
                + VALUE ( LEFT ( RIGHT ( 'Table'[_Total_total], 5 ), 2 ) ) * 60
                + VALUE ( RIGHT ( 'Table'[_Total_total], 2 ) )
    )
RETURN
    DIVIDE ( SUMX ( _a, [hm] ), MAXX ( _a, [total] ) )

 

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

  • dfs29's avatar
    dfs29
    Helper I
    3 years ago

    thanks, but I don t know why when I use only one row with my exemple the percentage is wrong : 

     

    It souhld be  100 % here and not 300 %

     

    this how I calculate 

    Percentage =
    VAR _a =
        ADDCOLUMNS (
            'Table',
            "hm",
                VALUE (
                    LEFT ( ACT_activite[_Total (hm)], ( FIND ( ":", ACT_activite[_Total (hm)] ) - 1 ) )
                ) * 60 * 60
                    + VALUE ( LEFT ( RIGHT ( ACT_activite[_Total (hm)], 5 ), 2 ) ) * 60
                    + VALUE ( RIGHT ( ACT_activite[_Total (hm)], 2 ) ),
            "total",
                VALUE (
                    LEFT ( 'ACT_activite'[_Total_total], ( FIND ( ":", ACT_activite[_Total_total] ) - 1 ) )
                ) * 60 * 60
                    + VALUE ( LEFT ( RIGHT ( ACT_activite[_Total_total], 5 ), 2 ) ) * 60
                    + VALUE ( RIGHT ( ACT_activite[_Total (hm)], 2 ) )
        )
    RETURN
        DIVIDE ( SUMX ( _a, [hm] ), MAXX ( _a, [total] ) )