Average of distinct values

Answered

Comments

6 comments

  • Avatar
    Michael Becker

    Hi Sivan,

    I'm not sure I fully understand your issue, do you have problems with grand total for last measure being calculated wrong, or do you want to replicate that calculation as a single indicator?

     

    If you have issue with Grand Total, please check that grand total setting for last measure is set to auto and on the first two to sum. That should give you what you require.

    In terms of calculating a single indicator, I would need to know how exactly you calculate first two measures before I could hint anything. I suspect that it might be better to carry those calculations to ElastiCube. Can you share those calculations?

    Regards,

    Michał

    0
    Comment actions Permalink
  • Avatar
    Malinda Jepsen

    Another approach that I've used is to use the ALL() function in my calculation (see section "Calculating Contributions Using the ALL Function") in this article: https://documentation.sisense.com/creating-formulas-based-on-criteria-and-conditions-filters/  

    0
    Comment actions Permalink
  • Avatar
    sivan Zohar Avda

    hi,

    Thanks for the reply.

    I want to calculate a single indicator the shows the avarage of usage ("days of use" divded by"num of days in month") for all of the branch in the organization.

    the caculations for one branch is:

    days of use = count([days in logtime]) - this part works just for one branch 

    num of days in month  = [max of num_of_working_days] - this part works fine

    the grandTotal was an example of the single indicator that i want to calculate.

    the problem is when i expand the calculation of the indiactor for all the branches because the calculation just do distict on all the days od the month and not the avarage for every branch and the avarage all the results.

    does it makes sense?

    Sivan

     

    0
    Comment actions Permalink
  • Avatar
    Michael Becker

    @Sivan - I would follow Malinda's suggestion, if that won't work I think you would need to pre-calculate number of days in ElastiCube.

    Regards,

    Michał

    0
    Comment actions Permalink
  • Avatar
    David Wit

    One of the aggregate averaging functions may work for you.  I have used it in a similar use case. 

    https://documentation.sisense.com/building-formulas-with-functions/ 

    Average

    Avg(<numeric Field>)

    Calculates the mean average of the given values.
    For example – AVG(Score) will calculate the mean average of the given scores.

    Avg(<group by field>, <aggregation>)

    Calculates the average of the given aggregation grouped by another filed.
    For example – Avg( Product, Total Sales) will calculates the average of the total sales per product.

    https://documentation.sisense.com/function-reference/ 

    0
    Comment actions Permalink
  • Avatar
    sivan Zohar Avda

    It worked :) thanks

    0
    Comment actions Permalink

Please sign in to leave a comment.