Exchange measures

[terpilka]

Hi all, 

Could you help me, please,


Could you share your experience how I can realize the follow logic:
If I filter region=region2
When I create a pivot table in PowerBI  with filter I want to calculate:

for  "old" in indicator ="175"
FOR "new " in indicator ="45"

Please find the attachment as an example from Excel.

I try to solve it in the follow way:
DurationNew = if(VALUES('Project Tasks'[indicator])="ALL",CALCULATE(SUM('Project Tasks'[TaskDuration]),FILTER('Project Tasks','Project Tasks'[indicator]="old"),ALL('Project Tasks'[Region])),
CALCULATE(SUM('Project Tasks'[TaskDuration]),FILTER('Project Tasks','Project Tasks'[indicator]="new"),ALLselected('Project Tasks'[Region])))

But then I use filter in region, I have both of them filtered.
I need the table in this format, nobody don't use the column "indicator" as a filter.

Many thanks, 
BR, Valeria.

[v-lili6-msft · Accepted solution]

hi,@terpilka

      After my research, you can do these follow my steps like below:

Step 1:

use this formula to add a new table and create the relationship with basic table

regions = VALUES('Project Tasks'[region])

Step 2:

create a measure by using formula like below:

Measure 4 = if(min('Project Tasks'[indicator]) = "old",CALCULATE( sum('Project Tasks'[TaskDuration]), all(regions[region])), if(min('Project Tasks'[indicator]) = "new", sum('Project Tasks'[TaskDuration]), 999))

Step3:

Darg field regions from table Regions into slicer visual

Result:

here is demo, please try it.

https://www.dropbox.com/s/awpgenjbhp5ewje/new%20exchange.pbix?dl=0

Best Regards,

Lin

[terpilka]

Many thanks,

It works.

BR, 

Valeria!