Forum Discussion

mizaskun's avatar
mizaskun
Helper II
4 years ago
Solved

Get correct subtotal in simple measures

Good morning,

 

I am trying to calculate a simple measure subtotal, but I am having problems doing so. 

 

I have the following simple table:

 

I am trying to calculate the POND 1 and POND 2 %.

 

I am doing so applying the following formulas:

P1 = sum(PRUEBA[POND 1]) / calculate(sum(PRUEBA[POND 1]), all())
P2 = sum(PRUEBA[POND 2]) / calculate(sum(PRUEBA[POND 2]), all())
 
Then I need to get the product of both %. This is:

 

I am doing so by applying the formula: 

P1*P2 = PRUEBA[P1]*PRUEBA[P2]
 
And this is the result:

 

When I try to calculate the new % over the product, it uses the red circled 1,00 as the denominator. I would need this behaviour:

 

2 / 25 ... 11 / 25 .... and so on.. but it is using 2 / 100 ... 11 / 100 ...

 

 

Thank you so much! 

 

Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    mizaskun , Create a measure like

     

    sumx(summarize(Table, Table[persona], table[Region], Table[RIEGO], "_1",[P1]*[P2]) , [_1])

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    mizaskun , Create a measure like

     

    sumx(summarize(Table, Table[persona], table[Region], Table[RIEGO], "_1",[P1]*[P2]) , [_1])

    • mizaskun's avatar
      mizaskun
      Helper II
      4 years ago

      It works! Great! Thank you!

       

      I need to calculate now the sum of the different P% that we have just calculated with your formula * the VAL field. I am trying something like: 

      TOTAL = PRUEBA[P%]*sum(PRUEBA[VAL]), but it sums 11. How could I get the correct value? 

       

      Thank you so much!