Skip to main content
cancel
Showing results for
Search instead for
Did you mean:
Creator III

## SUM with Division

Hello,

I have the following Expression:

SUM([Sales A] / [Sales B]) // working - when I clear all filters (for example Customers) I see the total value

SUM([Sales A]) / SUM([Sales B]) // not working - when I clear all, I see a number that's not the total value

The thing is I made an optimization to the Data Model and now I would have to use something like:

SUM({<METRIC={'ACTUAL'}>}Sales) / SUM({<TYPE={'B'}>}Sales) - but it's not working because I don't get the total (it just works when I filter a Customer for example)

Is there any way to make the division in the same function? Or any way instead to get the same result?

Thank you!!!

--

Here's a demo:

 ID TYPE METRIC VALUE 1 ACTUAL MET1 10 1 BUDGET MET1 30 1 ACTUAL MET2 15 1 BUDGET MET2 55 2 ACTUAL MET1 5 2 BUDGET MET1 33 2 ACTUAL MET2 45 2 BUDGET MET2 85

My expression where I need to divide value with metric1 over metric2 (works ONLY when I select an ID):

=SUM({<TYPE={'ACTUAL'},METRIC={'MET1'}>}VALUE)
/ SUM({<TYPE={'ACTUAL'},METRIC={'MET2'}>}VALUE)

for ID = 1 I get 0.66

for ID = 2 I get 0.11

but when nothing selected it shows 0.25 and it should show 0.66+0.11 = 0.77

5 Replies
Champion III
Can you share some sample data with an expected output ?
Creator III
Author

Here's an example:

 ID TYPE METRIC VALUE 1 ACTUAL MET1 10 1 BUDGET MET1 30 1 ACTUAL MET2 15 1 BUDGET MET2 55 2 ACTUAL MET1 5 2 BUDGET MET1 33 2 ACTUAL MET2 45 2 BUDGET MET2 85

My expression where I need to divide value with metric1 over metric2 (works ONLY when I select an ID):

=SUM({<TYPE={'ACTUAL'},METRIC={'MET1'}>}VALUE)
/ SUM({<TYPE={'ACTUAL'},METRIC={'MET2'}>}VALUE)

for ID = 1 I get 0.66

for ID = 2 I get 0.11

but when nothing selected it shows 0.25 and it should show 0.66+0.11 = 0.77

Champion III
May be try using TOTAL qualifier like Sum(Total ...)/Sum(Total ....)
After total write your set expr
MVP

Try this

```Sum(Aggr(
Sum({<TYPE = {'ACTUAL'}, METRIC = {'MET1'}>} VALUE)/Sum({<TYPE = {'ACTUAL'}, METRIC = {'MET2'}>} VALUE), ID))```
Partner - Champion III

These two expressions are NOT the same arithmetically:

SUM([Sales A] / [Sales B])                   -- sum of ratios (sum of rows total)
SUM([Sales A]) / SUM([Sales B])      -- ratio of sums (expression total)

That's why the result differ.  If you want the sum of rows total in a pivot table, the Aggr() logic posted by Sunny should work.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Community Browser