Forum Discussion
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 Percentage_of_something = divide(Numerator,Denominator) but this second matrix (a copy from the first) is aggrageting my number via Sum.
Actuall if I right click on the value there is no aggregate option only Conditional formatting, Quick Clac and Remove Field
is there something in my new formula that made it behave that way... okay here are the formula:
Matrix A (working nicely)
Percentage_of_A = DIVIDE(a_numerator,by_the_denominator)
Matrix B on the other hand (Total is sum of the averages - not working)
Percentage_of_B = COUNTROWS(FILTER('Table',[result_from_a_new_measure] > .75)) / 48
***48 is a static number, and the numerator can be 1 to 48
Is there something in that formula that made the matrix behave like that? summing up the percentage instead of getting the percentage?
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!!!
2 Replies
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:
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!!!