Forum Discussion
Exchange measures
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.
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
2 Replies
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