Average Count base from two Column

HI AllanBerces ,

You can get the two “average counts” you want by using measures (not a calculated column) so the result is dynamic with filters. The idea is:

Count the Direct and In Direct trades for the group (Location-Vessel or TradeBP × Location-Vessel).
Divide that by the number of weeks in the data for that same group (distinct WeekNo, or weeks up to the current context if you prefer).

Assumptions (you can adapt the names)

Table name: Trades
Columns: WeekNo, LocationVessel, TradeBP (e.g., Mech, Elec, Civil), Trade (Direct or In Direct)

Ave Direct/InDirect per Location-Vessel

What it does: for each Location-Vessel, average the number of Direct/In Direct trades per week.

DAX (measure)

Direct/Indirect count for the current Location-Vessel VAR DirectIndirectCount = CALCULATE( COUNTROWS(Trades), Trades[Trade] IN {"Direct","In Direct"}, ALLEXCEPT(Trades, Trades[LocationVessel]) )

Distinct weeks for that Location-Vessel VAR WeekCount = CALCULATE( DISTINCTCOUNT(Trades[WeekNo]), ALLEXCEPT(Trades, Trades[LocationVessel]) ) RETURN DIVIDE(DirectIndirectCount, WeekCount)

Ave Direct/InDirect per Trade BP × Location-Vessel

What it does: same idea as above, but split by TradeBP as well (so you get a separate average per combination of TradeBP and Location-Vessel).

DAX (measure) VAR DirectIndirectCount_BP = CALCULATE( COUNTROWS(Trades), Trades[Trade] IN {"Direct","In Direct"}, ALLEXCEPT(Trades, Trades[TradeBP], Trades[LocationVessel]) ) VAR WeekCount_BP = CALCULATE( DISTINCTCOUNT(Trades[WeekNo]), ALLEXCEPT(Trades, Trades[TradeBP], Trades[LocationVessel]) ) RETURN DIVIDE(DirectIndirectCount_BP, WeekCount_BP)

Notes and tips

If you want the denominator to be the number of weeks up to the current week (e.g., weeks 1..N in your filter), use a running-week approach for WeekCount, e.g.:
WeekCount = CALCULATE(DISTINCTCOUNT(Trades[WeekNo]), FILTER(ALLSELECTED(Trades), Trades[WeekNo] <= MAX(Trades[WeekNo])))
If your data already uses “In Direct” (with a space) or “Direct”, make sure the values match exactly in the IN {...} list (or normalize with a clean column).
Use a matrix visual:
Rows: TradeBP, LocationVessel (for the second measure)
Values: the two measures above
You can add WeekNo to the rows or as a slicer if you want to inspect per-week behavior, but the measures themselves give you the average per group.

Please mark this post as solution if it helps you. Appreciate Kudos.