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

[AnnieTay]

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.

[v-yajiewan-msft]

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.

[AnnieTay]

Thank you so much @v-yajiewan-msft , 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?

[v-yajiewan-msft · Accepted solution]

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.

[AnnieTay]

Thank you @v-yajiewan-msft , much appreciated!

[bhanu_gautam]

@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
)

[AnnieTay]

@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.