none
How to get the measure total at row level for another measure calculation in DAX

    Question

  • Hi There,

    Using DAX, I am trying to get an expression for a calculated measure as follow:

    So basically, I would like to get the AMeasure total highlighted in yellow in the A2Measure calculation:

    Smeasure -(Ameasure total * WMeasure) where Ameasure total is the one highlighted. 

    I think my question would be how to get the total of a measure in order to used in the calculation of another measure.

    Sorry but I forgot to add the following:

    I cannot use the solution proposed below: AMeausure_Total:=CALCULATE([AMeasure], ALL('Yourtable'[Payer]))

    Because I should be able to get the total total highlighted taking into account that there are some filters that can apply to the table:

    Customer Filter

    Payer Filter

    Calendar Filter

    What I mean is that the grand total I need is the one for the table that is produced after applying the filters, in the example:

    Customer Filter: Customer1

    Payer Filter: All

    Calendar Filter: May 2013

    I hope this clarify a little bit more what I need,

    Thanks and best regards,

    Joss


    • Edited by Joss83 Friday, January 31, 2014 9:28 AM calrification on requirements
    Thursday, January 30, 2014 4:48 PM

Answers

  • try this, it ignores all selections on rows and columns but still considers your filters

    AMeausure_Total:=CALCULATE([AMeasure], ALLSELECTED())


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    • Marked as answer by Joss83 Friday, January 31, 2014 9:52 AM
    Friday, January 31, 2014 9:50 AM

All replies

  • this should work:

    AMeausure_Total:=CALCULATE([AMeasure], ALL('Yourtable'[Payer]))

    it calculates the measure [AMeasure] but ignores all filters on [Payer] resulting in the same value (=total value) for all your rows

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Friday, January 31, 2014 8:22 AM
  • Thanks Gerhard for your help, and sorry but I have clarified a little bit the requirements, just in case you can help me with that.

    The Grand Total I need is the table total after being applied some filters like Customer, Payer and Calendar.

    Hope you can help me with that,

    Best regards,

    Joss

    Friday, January 31, 2014 9:30 AM
  • try this, it ignores all selections on rows and columns but still considers your filters

    AMeausure_Total:=CALCULATE([AMeasure], ALLSELECTED())


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    • Marked as answer by Joss83 Friday, January 31, 2014 9:52 AM
    Friday, January 31, 2014 9:50 AM
  • Thanks Gerhard, thanks to your hint in your post before I came up with the same solution.

    Friday, January 31, 2014 9:52 AM