applying calculated field when filtering

[jagdeep]

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,

[JoyCornerstone]

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

[jagdeep]

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

[v-frfei-msft · Accepted solution]

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]

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]

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]

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