CALCULATE TOTAL RATIO WITH SOME DATA = 0

philgood34 — Fri, 29 Sep 2017 17:40:29 GMT

let me introduce my issue

As you see in these entreprise, there are 2 categories of employees :

COM who produces sales

STAFF who does'nt

Of course the 2 categories consume hours.

I have to calculate the ratio SALES per HOUR for each sector and total

i use this expression :

If(Sum(SALES) = 0, Sum(TOTAL SALES)/ Sum(HOURS), Sum(SALES)/Sum(HOURS))

(to simplify i took a simple example without needding Aggr & dimensionnality ()=0)

As you see, for staff i need total sales to calculate the ratio:

Here comes my issue :

when i have to analyse one COM sector together with one STAFF sector the result is

As you see the ratio is correct for total and COM but not for STAFF

i need the expression to "freeze" the total SALES independently of sector's selection ...

I don't know the expression for that

hoping some help

regards

Philippe

Re: CALCULATE TOTAL RATIO WITH SOME DATA = 0

philgood34 — Fri, 29 Sep 2017 19:47:41 GMT

got it !

If(Sum(SALES) = 0, Sum({1}TOTAL SALES)/ Sum(HOURS), Sum(SALES)/Sum(HOURS))

i hope it will be useful for other new users

regards

Philippe