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

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?