locked
Row filter vs. calculate RRS feed

  • Question

  • I wrote this calculate function:

    Calculated Field 1:=CALCULATE([NumberofContracts],OR(COBI_COContract[RenewalType]="Auto Renewal",COBI_COContract[RenewalType]="Opt-In Renewal"))

    In my pivot table I have a row called status and then the subcategory is "renewal type" (referenced above). The two statuses are cancelled and expired and subcategories are auto renewal and opt in renewal (referenced above). When I put my calculated measure in this table, the value for aut renewal and opt in renewal are the same regardless of the row filter for renewal type. It just shows the amount for the status total. Why is this? 


    Saturday, December 10, 2016 1:46 AM

Answers

  • Because calculate overwrites the filter c text coming form the table. That is what it does. Why don't you just use [number of contacts]?

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    Saturday, December 10, 2016 1:50 AM
    Answerer
  • Hi AlexMartini,

    You want to display the format as Cancelled is 300, the Auto Renewal is 140, Opt-In Renewal is 160, right? You should not add filter in "Calculated Field 1" measure. You'd better try to use "NumberofContracts" measure and check if it is fine. Or please post the sample data for further analysis. 


    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 12, 2016 1:52 AM

All replies

  • Because calculate overwrites the filter c text coming form the table. That is what it does. Why don't you just use [number of contacts]?

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    Saturday, December 10, 2016 1:50 AM
    Answerer
  • Hi AlexMartini,

    You want to display the format as Cancelled is 300, the Auto Renewal is 140, Opt-In Renewal is 160, right? You should not add filter in "Calculated Field 1" measure. You'd better try to use "NumberofContracts" measure and check if it is fine. Or please post the sample data for further analysis. 


    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 12, 2016 1:52 AM