Calculated field and filtering

[MikiD]

Hello,

I have a doubt with a calculated field.

I have a TASK table with this information:

TASK_VALUES (date, task, value)

1.2.2020, TASK1, 120

1.2.2020, TASK2, 30

1.2.2020, TASK3, 200

1.2.2020, TASK4, 40

10.2.2020, TASK1, 300

....

And a second table with TASK_DISTRIBUTION

TASK_DISTRIBUTION(task, family, %F)

TASK1, FAM1, 80%

TASK1, FAM2, 20%

TASK2, FAM3, 80%

TASK2, FAM4, 20%

TASK3, FAM3, 80%

TASK3, FAM4, 20%

TASK4, FAM5, 40%

TASK4, FAM2, 60%

And I would like to have a new calculated filed in TASK_VALUES table in this way:

Value_family = SUMX(FILTER(TASK_DISTRIBUTION;TASK_VALUES[Task]=TASK_DISTRIBUTION[Task]);TASK_DISTRIBUTION[%F]*[Value]/100)

It works without report filters, but when I filter by “family” this filter is not taken into account and the result is the same that without filter.

I must answer questions like:

- “value for family FAM1 on day 1.1.2020” --> 96

- “value for family FAM2 on day 1.1.2020” --> 48 (24+24)

- “family distribution values on day 1.1.2020” --> FAM1-96, FAM2-48, FAM3-184, FAM4-46, FAM5-16 (Total 390)

What change should I have to make in the formula?

Thanks in advance!

[Anonymous]

Well, actually you can do that.

Drag to filter. Select the "Condition" tab. Suppose your paramater is named "Chosen department". You could create a condition like:

SUM(IF [department] = [Chosen department] THEN 1 END) > 0

This will basically get all the clients that have at least 1 sale on that chosen department

[Stachu]

How does your data model look like, are there any relationships between these tables? Do you have a table with unique Tasks linked to both of these? If not 

Also can you add change sample tables to format that can be copied to PowerBI? Like this (just copy and paste into the post window).

Column1	Column2
A	1
B	2.5

[MikiD]

Hello Stachu !

Model looks like this:

TASK_VALUES with TASK_DISTRIBUTION as many-to-may

TASK with TASK_VALUES as one-to-many

FAMILY with TASK_DISTRIBUTION as one-to-many

TASK_DISTRIBUTION

Task Family %F

TASK1	FAM1	80
TASK1	FAM2	20
TASK2	FAM3	80
TASK2	FAM4	20
TASK3	FAM3	80
TASK3	FAM4	20
TASK4	FAM5	40
TASK4	FAM2	60

TASK_VALUES

Date Task Value Value_family

dimecres, 1 de gener de 2020	TASK1	120	120
dimecres, 1 de gener de 2020	TASK2	30	30
dimecres, 1 de gener de 2020	TASK3	200	200
dimecres, 1 de gener de 2020	TASK4	40	40
dilluns, 10 de febrer de 2020	TASK1	300	300
dilluns, 10 de febrer de 2020	TASK3	150	150

FAMILY

Family Description

FAM1	Description family 1
FAM2	Description family 2
FAM3	Description family 3
FAM4	Description family 4
FAM5	Description family 5

TASK

Task Description

TASK1	Description task1
TASK2	Description task2
TASK3	Description task3
TASK4	Description task4

is this enough information for you? do you need something else?

Thanks !

[Stachu · Accepted solution]

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 )

 

[MikiD]

OMG ! It works perfect. It shows that you are an expert. Many thanks.