locked
SUMX with different filters RRS feed

  • Question

  • Hi All

    I need to compute SALES VALUE from 2014 but with prices from 2015.

    Here is the measure:

    SALES VALUE 2014 (2015 prices):=CALCULATE(SUMX(PC;PC[Items]*PC[AVG PRICE (LC)]))

    How to adjust this measure with proper filters because currently it calculates:

    SALES VALUE from 2015 with prices from 2015  and

    SALES VALUE from 2014 with prices from 2014


    Monday, June 6, 2016 8:46 AM

Answers

  • Hi Bartek,

    You can try this:

    SALES VALUE 2014 (2015 prices):=
    IF(
      HASONEVALUE(PC[Year]), 
      IF(
        VALUES(PC[Year]) = 2015, 
        CALCULATE(
          [SALES ITEM] * CALCULATE(
                           DIVIDE([SUM VALUE (LC)], [SALES ITEM]), 
                           PC[Year] = 2015
                         ),
        PC[Year] = 2014)
      )
    )

    I've written the DAX in a way that mimics the logic that you've illustrated (i.e. reusing your existing calculated fields/measures). The IF function has been used to ensure that a value only appears against the year 2015 and as a result you won't see a grand total for this measure.


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Tuesday, June 7, 2016 9:20 AM

All replies

  • Hi Bartek,

    Something along these lines could work...

    SALES VALUE 2014 (2015 prices):=

    CALCULATE( SUMX( ADDCOLUMNS( PC, "2015Price", CALCULATE( MAX(PC[AVG PRICE (LC)]), FILTER( ALL(PC), PC[Year] = 2015 ) ) ), PC[Items] * [2015Price] ), PC[Year] = 2014 )

    ...But without knowing much about your model or data, it's hard to put together and test a working solution. If the above doesn't behave as expected, please provide more details of the tables that take part in this scenario and provide some example data. That way, another member of this forum or myself will be able to help you further.


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    • Edited by Michael Amadi Monday, June 6, 2016 3:25 PM Minor edit
    Monday, June 6, 2016 11:08 AM
  • Thanks Michael

    This is some how working but not yet correctly.

    Here is The datamodel:

    And here is a data view of PC table. I have filtered one SKU (product name) to show how it is look like:

    [AVG PRICE (LC)] is a calculated column.

    2m_period is "two months period" (JAN-FEB..). So sales data are shown bimonthly.

    Here is a logic how this measure should be computed based on this one SKU filtered from PC table:

    Sorry if I was not precise. Is this cleaner now?

    Monday, June 6, 2016 4:52 PM
  • Hi Bartek,

    You can try this:

    SALES VALUE 2014 (2015 prices):=
    IF(
      HASONEVALUE(PC[Year]), 
      IF(
        VALUES(PC[Year]) = 2015, 
        CALCULATE(
          [SALES ITEM] * CALCULATE(
                           DIVIDE([SUM VALUE (LC)], [SALES ITEM]), 
                           PC[Year] = 2015
                         ),
        PC[Year] = 2014)
      )
    )

    I've written the DAX in a way that mimics the logic that you've illustrated (i.e. reusing your existing calculated fields/measures). The IF function has been used to ensure that a value only appears against the year 2015 and as a result you won't see a grand total for this measure.


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Tuesday, June 7, 2016 9:20 AM