locked
Power Pivot calculated function help RRS feed

  • Question

  • When I put this in a power pivot that has months as the rows, it returns the same value for every month (the sum of May and June). Without the || it returns nothing for each month because it filters for May and June which doesn't exist, but it seems like it should only return values for May or June and nothing else, not the same value for every month.
    CALCULATE([Total Sales], Calendar[EnglishMonthName]="May"|| Calendar[EnglishMonthName]= "June")


    • Edited by Olaf HelperMVP Friday, October 7, 2016 4:49 PM Format fixed
    Friday, October 7, 2016 9:49 AM

Answers

  • Hello Alex,

    Please always post more details about your data model, some sample data and the expected result, otherwise it's difficult to guess what you have and how to assist your.

    I guess here you have used the formula for a measure calculation and in that case you get for every month the same result, because there is no date relation in the formula.

    If you only want to show sales for the both months you can use a calculated column instead with the DAX formula

    =IF(Sales[EnglishMonthName] = "May" || Sales[EnglishMonthName] = "June", Sales[Total Sales], BLANK() )

    Some sample data and result:

    If that's not what your are looking for, then please explain your requirements more details ... with the already requested examples/result.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, October 7, 2016 5:31 PM

All replies

  • I guess the calculated filter overrides the context BUT what if I want the month context but just don't want to return values for certain months? Let's say in the case above, I just want values to show up if it May or June. So all months would be empty except May or June. 
    Friday, October 7, 2016 9:56 AM
  • Hello Alex,

    Please always post more details about your data model, some sample data and the expected result, otherwise it's difficult to guess what you have and how to assist your.

    I guess here you have used the formula for a measure calculation and in that case you get for every month the same result, because there is no date relation in the formula.

    If you only want to show sales for the both months you can use a calculated column instead with the DAX formula

    =IF(Sales[EnglishMonthName] = "May" || Sales[EnglishMonthName] = "June", Sales[Total Sales], BLANK() )

    Some sample data and result:

    If that's not what your are looking for, then please explain your requirements more details ... with the already requested examples/result.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, October 7, 2016 5:31 PM