Forum Discussion

ovetteabejuela's avatar
ovetteabejuela
Impactful Individual
9 years ago
Solved

Matrix Visual Aggregate From Average to Sum?

I have a Matrix visual that is working well it take the average well..   I copied that visual and change the value to another value which is actually the same pattern as the original formula was Pe...
Need Help
  • ovetteabejuela's avatar
    ovetteabejuela
    9 years ago

    Okay, I resolved this but I can't explain why it does what it does before I fixed it.

     

    The most logical answer for me is that both the numerator and the denominator were not extracted or was not derived the same way such as:

     

    Result = DIVIDE(47/48) works but

    Result = DIVIDE(COUNTROWS(FILTER('Table',[result_from_a_new_measure] > .75)) , 48) doesn't

     

    The solution: since the numerator can't be static, I made the denominator dynamic and the final formula ended up like

     

    Result = DIVIDE(COUNTROWS(FILTER('Table',[result_from_a_new_measure] > .75)) , COUNTROWS('Table'))

     


     

    Happy!!!

ovetteabejuela's avatar
ovetteabejuela
Impactful Individual
9 years ago

okay, I can confirm that it is the numerator that is making it act that way:

 

if I do this:

     Result = COUNTROWS(FILTER('Table',[result_from_a_new_measure] > .75)) / 48

or this:

     Result = DIVIDE(COUNTROWS(FILTER('Table',[result_from_a_new_measure] > .75)) , 48)

the aggregate turns to a SUM behind the scene

 

but if it's something like this:

     Result = 47/48

the aggregation operation is AVERAGE, as expected.

 

I can't tell what could be the reason, anyone here can help me sort this out?

 

Thank you....

 

Edit: Added this screenshot:

 

  • ovetteabejuela's avatar
    ovetteabejuela
    Impactful Individual
    9 years ago

    Okay, I resolved this but I can't explain why it does what it does before I fixed it.

     

    The most logical answer for me is that both the numerator and the denominator were not extracted or was not derived the same way such as:

     

    Result = DIVIDE(47/48) works but

    Result = DIVIDE(COUNTROWS(FILTER('Table',[result_from_a_new_measure] > .75)) , 48) doesn't

     

    The solution: since the numerator can't be static, I made the denominator dynamic and the final formula ended up like

     

    Result = DIVIDE(COUNTROWS(FILTER('Table',[result_from_a_new_measure] > .75)) , COUNTROWS('Table'))

     


     

    Happy!!!