1 Reply Latest reply: Sep 29, 2017 3:47 PM by Philippe BONNIN RSS

    CALCULATE TOTAL RATIO WITH SOME DATA = 0

    Philippe BONNIN

      hi

       

      let me introduce my issue

       

      structure.png 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:

       

      TOTAL.png

       

      Here comes my issue :

       

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

       

      partiel.png

      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