Post reply

Issue with a sum of percentages

  • October 15, 2009 at 9:42 am

    #205640

    Hi,

    This maybe straight forward, but i don't do a lot of cube work.

    Ok, in my fact table i have a 88% level of service for a resturant unit for a week

    Unit Week Value Type

    1234 48 88 SER%

    However displaying the data, it sums up all the percentages of all the units for all the weeks, so i end up with a value of 39,000

    I've had a go at creating a calculated member using avg, but couldn't get the systax right, or even if its the right way to go.

    I think i need to count the number of unit displayed, and the week, for it to display the value right?

    I have a dimension of the units call Location, in hireachies of regions, brand, unit ID

    And i have a dimenson TypeVersion, of the types SER%, WAGES

    as i store a few different values in the fact data

    can anyone help??

    thanks

    Jon

  • October 15, 2009 at 11:01 am

    #1066397

    Not sure what level of control you have over your environment, but, assuming 'a lot' I would recommend changing your data model to break the facts out into columns rather than re-using the single column and 'slicing' the measure. The problem with slicing that measure is, let's say you change the aggregation method on 'Value' to be average. Ok, works (somewhat) great on the percentages, but now your wages will also aggregate by average, quite possibly not what your end users want.

    If you fact looked more like...

    Unit | Week | Wages | ServicePct | <other measures>

    you could then include Wages and ServicePct as individual measures, and control them appropriately (ie roll up wages as SUM and ServicePct as AVG). You could even work further back from this and supply the values that go in to determining the service percent (ie if svcpct == hrs of complaints / hrs worked then bring those two measures in, so you can use them standalone and then add a calc measure to create the percentage.

    Steve.

  • October 19, 2009 at 5:24 am

    #1067576

    Hi Steve,

    thanks for the info thats very helpful, i'll have a go at breaking out the measures from one column into a few, see where i go from there

    thanks

    Jon

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply