Forum Discussion
Calculated field and filtering
If values need to change with slicer selection then you cannot use a calculated column, it has to be a measure.
Few changes are needed here:1) change relationships to what you see below, in visuals and formulas only use relating to tasks use only columns from 'TASK' table as it will propagate filter to TASK_DISTRIBUTION and TASK_VALUES
it removes the bidirectional relationship (which is usually a bad practice, more details here https://www.sqlbi.com/tv/understanding-relationships-in-power-bi/ around 14:30)
2) add this measure (it will work with relationships like above)
Measure = VAR __ValuesWithPercent = ADDCOLUMNS ( 'TASK_VALUES', "%", CALCULATE ( SUM ( 'TASK_DISTRIBUTION'[%F] ) ) ) RETURN SUMX ( __ValuesWithPercent, [Value] * [%] / 100 )
If values need to change with slicer selection then you cannot use a calculated column, it has to be a measure.
Few changes are needed here:
1) change relationships to what you see below, in visuals and formulas only use relating to tasks use only columns from 'TASK' table as it will propagate filter to TASK_DISTRIBUTION and TASK_VALUES
it removes the bidirectional relationship (which is usually a bad practice, more details here https://www.sqlbi.com/tv/understanding-relationships-in-power-bi/ around 14:30)
2) add this measure (it will work with relationships like above)
Measure =
VAR __ValuesWithPercent =
ADDCOLUMNS ( 'TASK_VALUES', "%", CALCULATE ( SUM ( 'TASK_DISTRIBUTION'[%F] ) ) )
RETURN
SUMX ( __ValuesWithPercent, [Value] * [%] / 100 )
OMG ! It works perfect. It shows that you are an expert. Many thanks.