How to make dimension filters work, even when they are not present in LOD functions

here is the setup I am working with: In the data source, each row represents a value for a single customer, which is a sum of their performance for that month. So if it were sales, each row would be sum of sales for that customer for that month.
There are two divisions for which I am tracking a measure over multiple months, and I need to show average sum of the measure per month for one or both divisions, depending on the filter combination.

What I need is a single calculated field that could do this:

If both divisions are selected, then the value should be: average(sum(division1) + sum(division2)) per month for the selected months in the filter.
If only one division is selected, then it should be average per month for that division only (so average(sum(division1))

Let us assume these are the values I have per division: (have in mind this is a screenshot from excel, I explained earlier how the data source table looks like, this is just for example purposes)

I will assume all months are selected:

If both divisions are selected in the division dimension filter, then I would expect bolded value: 48 171
If one division is selected, I would expect either 32 602 or 15 569 depending on the selection.

For different selection of the month dimension filter, calculation should apply for the selected months.

I have tried something in this direction (with many variations):

avg( {FIXED [month] : sum(if [month]>=202301 and [month]<=202306 and [div_id] in (1,2) then [my_measure] else 0 end )
} )

(i put those conditions in IF clause, as I am observing only those months and those divisions for that particular calculated field, the data source contains more data, and are needed in the view).

This gives me correct values for case under 1) and filter on month works as intended, but of course division filter will not work, as div_id is not put in LOD statement.

If I put div_id in LOD, then I get sum of averages, not average of the sum. I figure maybe something with INCLUDE or EXCLUDE might do the trick, but I was headbanging for quite a while without success.

Solution

You can accomplish this using a combination of Custom Parameters and Calculated Fields.

Create Parameter

Create a new parameter, name it [Division Choice]. Change the data type to integer and change allowable values to list.
From here you'll see a table pop up; edit it so it's similar to:

Value Description

1 Division 1

2 Division 2

3 Division 1 & 2

Value	Description
1	Division 1
2	Division 2
3	Division 1 & 2

Create the Calculations

Let's create a new calculation named [Division Swap] that returns a respective Division Value when a corresponding parameter value is chosen.
```
CASE [Division Choice]
WHEN 1 THEN AVG([division 1])
WHEN 2 THEN AVG([division 2])
WHEN 3 THEN AVG([division1]+[division2])
END
```
Now bring [Division Swap] to your columns shelf or the text object in the Marks Pane.

Use it

Finally right click your parameter [Division Choice] and select show parameter. Change the selections on it and test the output in your worksheet or dashboard.

As long as your month filters are applied to the sheet, this should return the division values for whatever months you have selected.