topic Re: Allocate expenses in one region to others, regardless of filter context in DAX Commands and Tips

Allocate expenses in one region to others, regardless of filter context

AnnieTay — Tue, 01 Oct 2024 12:48:26 GMT

Simplified sample file: pbix

I have two tables:

1. Staff: EmpID, Name, Region, Function

2. Expenses: EmpID, Expense

The requirement is to allocate all expenses from Region C equally to Region A and Region B, and the report should no longer show Region C.

I have partially achieved this through the following measure:

Expenses (Budget) =
// Expenses for Region C are equally allocated to Region A and Region B

VAR RegionExpense = CALCULATE(
SUM(Expenses[Expense]),
Staff[Region] = "Region C"
)

VAR TotalExpense = SUM(Expenses[Expense])
VAR SelectedRegion = SELECTEDVALUE(Staff[Region])

RETURN
SWITCH(
TRUE(),
SelectedRegion IN {"Region A", "Region B"}, TotalExpense + (RegionExpense / 2),
SelectedRegion IN {"Region C"}, BLANK(),
TotalExpense
)

This works, except when a user filters on two regions. For example, if they select Region A AND Region B, I would like the Function graph to essentially show Region A + Region B + Region C. If they select Region A and Region D, I would like the Function graph to show Region A + 1/2 Region C + Region D. Ideally, the measure should handle any combination of Region selections.

I have tried ALL, ALLSELECTED etc with no luck. Thank you in advance for your help.

Re: Allocate expenses in one region to others, regardless of filter context

bhanu_gautam — Tue, 01 Oct 2024 14:54:30 GMT

AnnieTay , Try using below DAX

Expenses (Budget) =
VAR RegionCExpense = CALCULATE(
SUM(Expenses[Expense]),
Staff[Region] = "Region C"
)

VAR TotalExpense = SUM(Expenses[Expense])
VAR SelectedRegions = VALUES(Staff[Region])
VAR RegionCount = COUNTROWS(SelectedRegions)

RETURN
SWITCH(
TRUE(),
"Region C" IN SelectedRegions,
IF(
RegionCount = 1,
BLANK(),
TotalExpense + (RegionCExpense / (RegionCount - 1))
),
TotalExpense
)

Re: Allocate expenses in one region to others, regardless of filter context

AnnieTay — Tue, 01 Oct 2024 15:15:02 GMT

bhanu_gautam Thanks for looking into this. Unfortunately it's not working, implemented as v2:

Unfiltered:

Filtered (one region):

Filtered (two regions):

Do you have any other ideas? Thank you in advance for your help.

Re: Allocate expenses in one region to others, regardless of filter context

Anonymous — Wed, 02 Oct 2024 09:32:43 GMT

Hi AnnieTay , hello bhanu_gautam, thank you for your prompt reply!

As I understand it, no matter how you combine the filters, in your measure, all the expenditures in Region C have been equally distributed to Region A and Region B, please explain your confusion clearly.

In addition, you could also try the following measure and let me know the result:

Measure = VAR RegionCExpense = CALCULATE( SUM(Expenses[Expense]), Staff[Region] = "Region C" ) VAR TotalExpense = SUM(Expenses[Expense]) VAR SelectedRegion = SELECTEDVALUE(Staff[Region]) VAR IsRegionASelected = ISFILTERED(Staff[Region]) && SELECTEDVALUE(Staff[Region]) = "Region A" VAR IsRegionBSelected = ISFILTERED(Staff[Region]) && SELECTEDVALUE(Staff[Region]) = "Region B" VAR RegionAExpense = IF(IsRegionASelected, TotalExpense + (RegionCExpense / 2), TotalExpense) VAR RegionBExpense = IF(IsRegionBSelected, TotalExpense + (RegionCExpense / 2), TotalExpense) RETURN SWITCH( TRUE(), SelectedRegion = "Region A", RegionAExpense, SelectedRegion = "Region B", RegionBExpense, SelectedRegion = "Region D", TotalExpense, SelectedRegion = "Region C", BLANK(), TotalExpense )

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Allocate expenses in one region to others, regardless of filter context

AnnieTay — Wed, 02 Oct 2024 10:12:23 GMT

Thank you so much Anonymous , that works well! Would you be able to assist in adjusting the measure so that the Total also reflects the selected Regions? For example, if I select Region A & Region B, the total should be 1,400. If I select Region B & Region D, the total should be 750 etc?

Re: Allocate expenses in one region to others, regardless of filter context

Anonymous — Tue, 08 Oct 2024 08:15:50 GMT

Hi AnnieTay ,

Thank you for your feedback.

Please try the Measure2 as shown below:

Measure2 = VAR RegionCExpense = CALCULATE( SUM(Expenses[Expense]), Staff[Region] = "Region C" ) VAR TotalExpense = SUM(Expenses[Expense]) VAR SelectedCount = COUNTROWS(ALLSELECTED(Staff[Region])) VAR HasRegionA = CONTAINS(VALUES(Staff[Region]), Staff[Region], "Region A") VAR HasRegionB = CONTAINS(VALUES(Staff[Region]), Staff[Region], "Region B") VAR HasRegionC = CONTAINS(VALUES(Staff[Region]), Staff[Region], "Region C") VAR HasRegionD = CONTAINS(VALUES(Staff[Region]), Staff[Region], "Region D") VAR RegionAExpense = IF(HasRegionA, TotalExpense + (RegionCExpense / 2), TotalExpense) VAR RegionBExpense = IF(HasRegionB, TotalExpense + (RegionCExpense / 2), TotalExpense) RETURN IF( ISINSCOPE(Staff[Region]), SWITCH( TRUE(), HasRegionA, RegionAExpense, HasRegionB, RegionBExpense, HasRegionC, BLANK(), TotalExpense ), SWITCH( TRUE(), SelectedCount = 1 && HasRegionA, RegionAExpense, SelectedCount = 1 && HasRegionB, RegionBExpense, SelectedCount > 1 && HasRegionA && HasRegionB && HasRegionC, TotalExpense, SelectedCount > 1 && HasRegionA && HasRegionB && NOT HasRegionC, TotalExpense + RegionCExpense, SelectedCount > 1 && HasRegionA && HasRegionD && NOT HasRegionC, TotalExpense + RegionCExpense/2, SelectedCount > 1 && HasRegionB && HasRegionD && NOT HasRegionC, TotalExpense + RegionCExpense/2, SelectedCount > 1 && HasRegionA && HasRegionC, TotalExpense - (RegionCExpense / 2), SelectedCount > 1 && HasRegionB && HasRegionC, TotalExpense - (RegionCExpense / 2), SelectedCount > 1 && HasRegionC && NOT (HasRegionA && HasRegionB), TotalExpense - RegionCExpense, TotalExpense ) )

Result:

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Allocate expenses in one region to others, regardless of filter context

AnnieTay — Tue, 08 Oct 2024 13:23:53 GMT

Thank you Anonymous , much appreciated!