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 particul...
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

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.  

     

     

    • jagdeep's avatar
      jagdeep
      Regular Visitor
      7 years ago

      I've tried using CALCULATE, but i keep getting an error saying 'a single value for column 'population' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as max, min, count or sum to get a single result'  

      • JoyCornerstone's avatar
        JoyCornerstone
        Resolver II
        7 years ago

        Got it.  Did you try just making the R1 measure a Calculate function?

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    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