SUM with Division

[microwin88x]

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

[vishsaggi]

Can you share some sample data with an expected output ?

[microwin88x]

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

[vishsaggi]

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

[sunny_talwar]

Try this

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

[jonathandienst]

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.