Forum Discussion

jagdeep's avatar
jagdeep
Regular Visitor
7 years ago
Solved

applying calculated field when filtering

Hi there,

 

I have 2 calculated columns and 1 calculated measure.

 

When I don't apply any filters to the data, the calculations all work correctly - however, when I filter the data to a particular location, the calculated columns don't seem to calculate the numbers i want them to.

 

For example, i have Location A, B and C. When there is no filter, the calculated column and measure works fine, and uses the data from all 3 locations. Yet, when I filter to location A, i only want the calculated column and measure to use only the data associated with Location A - at the moment, it doesn't seem to be doing this and is using all the data, particularly when i look at the calculated columns

 

Can I please get some help with this?

 

Thanks,

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi jagdeep,

     

    I made one sample for your reference. We can create measures to work on it.

     

    Sales% new = CALCULATE(SUM('Relative Index'[Sales]))/CALCULATE(SUM('Relative Index'[Sales]),ALLSELECTED('Relative Index'))
    Population% new = CALCULATE(SUM('Relative Index'[Population])/CALCULATE(SUM('Relative Index'[Population]),ALLSELECTED('Relative Index')))
    RI NEW = [Sales% new]/[Population% new]

     

    For more details, please check the pbix as attached.

     

    Regards,

    Frank

6 Replies

  • JoyCornerstone's avatar
    JoyCornerstone
    Resolver II
    7 years ago

    Can you provide the calculations and maybe even an idea of how the data relates?

    • jagdeep's avatar
      jagdeep
      Regular Visitor
      7 years ago

      2 calculated columns

       

      Population% = 'Relative Index'[Population]/SUM('Relative Index'[Population])

      Sales% = 'Relative Index'[Sales]/SUM('Relative Index'[Sales])

       

      1 calcuated measure

       

      RI = SUM('Relative Index'[Sales%])/SUM('Relative Index'[Population%])*100

       

      The 'Population' and 'Sales' data refers to data from 3 locations, A, B and C. When I filter to location A, i want the above calculations to only include data from Location A etc.

       

      Thanks

      • JoyCornerstone's avatar
        JoyCornerstone
        Resolver II
        7 years ago

        I think on the 2nd one you need to use the CALCULATE function.  All of these could be measures:

         

        Population%=CALCULATE(
          DIVIDE('Relative Index'[Population],SUM('Relative Index'[Population])
)


Sales%=CALCULATE(
         DIVIDE('Relative Index'[Sales],SUM('Relative Index'[Sales])
)


R1 = CALCULATE(
         DIVIDE('Relative Index'[Sales%]),'Relative Index'[Population%])*100

        Then I believe it will filter based on the Location when you select it.