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 ...

Is there someone to help me please ? 

 

 

 

 

 

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.

4 Replies

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    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] ) )

       

  • dfs29's avatar
    dfs29
    Helper I
    3 years ago

    Hi v-jianboli-msft 

     

    First , thank you so much for your help !!!!

    And I have question why the total is false it should be 100% not 10.76% do yuou know why ? 

     

    Thanks 🙂