locked
measure for average not working correctly RRS feed

  • Question

  • Dear All

    My datatbale looks like below:

    Table: 'PTV FLAT HITLIST'

    Model BRAND SIZE/INCH Sales Units SALES THS.LC ..
    A Panasonic 32 200                     840
    B Panasonic 37 100                     420
    C Panasonic 40 300                  1 260
    D Panasonic 40 400                  1 680
    E LG 42 200                     840
    F LG 37 100                     420
    G LG 32 300                  1 260
    H Philps 32 400                  1 680
    I Philps 37 500                  2 100
    J Philps 30 566                  2 377
    K Philps 30 800                  3 360
    ...

    What I need is to create measure calculating average price for all brands and for SIZE/INCH greater than or equal  to 37.

    I wrote a measure:

    AVG PRICE 37 plus (LC):=CALCULATE(SUM([SALES THS LC])*1000/SUM([Sales Units 1]);ALL('PTV FLAT HITLIST'[BRAND]);FILTER('PTV FLAT HITLIST';'PTV FLAT HITLIST'[SIZEINCH]>=37))

    The problem is the measure is changing when I filter by brand. It should not change.

    What am I doing wrong?

    Gordonik

    Monday, October 6, 2014 1:20 PM

Answers

  • Alternatively to Greg2178's explanations you could try to get rid of that filter-part by using the following syntax:

    AVG PRICE 37 plus:=CALCULATE(SUM([SALES THS LC])*1000/SUM([Sales Units]);ALL('PTV Flat Hitlist'[BRAND]);'PTV FLAT Hitlist'[SIZE INCH]>=37)

    This will improve performance on big data sets but will only work with simple filters (e.g. you could not replace the value 37 with a more flexible measure).

    I hope this helps!

    Regards,

    Julian



    Julian Wissel | BI for NAV @ http://en.navbi.com | Blog @ blog.navida.eu


    Monday, October 6, 2014 8:13 PM

All replies

  • You have two filter arguments to your CALCULATE():

    ALL( 'PTV FLAT HITLIST'[BRAND] )

    and

    FILTER( 'PTV FLAT HITLIST'
        ; 'PTV FLAT HITLIST'[SIZEINCH] >= 37
    )

    All filter arguments to CALCULATE() are evaluated in a logical AND statement., Thus you are in two steps removing any context filters from 'PTV FLAT HITLIST'[BRAND], and subsequently bringing it back by filtering the table 'PTV FLAT HITLIST'.

    The table you are passing FILTER() respects current filter context, thus essentially overriding your ALL().

    What you need to do is move your ALL() into your FILTER():

    FILTER( ALL( 'PTV FLAT HITLIST' )
        ; 'PTV FLAT HITLIST'[SIZEINCH] >= 37
    )
    I do not know if this fits your exact needs as you have only shared a single table of your model with us, but this will ignore any filter context on your 'PTV FLAT HITLIST' table and return only those records where the [SIZEINCH] is >= 37.
    • Proposed as answer by Michael Amadi Monday, October 6, 2014 6:51 PM
    Monday, October 6, 2014 4:02 PM
  • Alternatively to Greg2178's explanations you could try to get rid of that filter-part by using the following syntax:

    AVG PRICE 37 plus:=CALCULATE(SUM([SALES THS LC])*1000/SUM([Sales Units]);ALL('PTV Flat Hitlist'[BRAND]);'PTV FLAT Hitlist'[SIZE INCH]>=37)

    This will improve performance on big data sets but will only work with simple filters (e.g. you could not replace the value 37 with a more flexible measure).

    I hope this helps!

    Regards,

    Julian



    Julian Wissel | BI for NAV @ http://en.navbi.com | Blog @ blog.navida.eu


    Monday, October 6, 2014 8:13 PM
  • Julian

    Thanks a lot. Your formula is better becouse it involve only BRAND and SIZE/INCH filters and allow to filter by others colums

    Anyway. Gents thanks for help

    G.

    Tuesday, October 7, 2014 9:16 AM
  • Just as an FYI, ALL() and ALLEXCEPT() allow fine grained control over the columns on which filter context is preserved/removed. It is worth looking into the syntax and usage of these and other context-altering functions, as sometimes you will face more complex filter criteria that cannot be implemented as a simple boolean argument to CALCULATE(), as Julian alluded to.
    Tuesday, October 7, 2014 1:33 PM