Average Count base from two Column

[AllanBerces]

Hi good day can anyone help me on my calculated Column/Measure. On my Table i need the average number of Trade Direct and InDirect base from two column the Location-Vessel and Trade BP's,. On my example i have a two weeks of Data. 

1st Column Desired output - Ave Direct/InDirect/Location-Vessel

• 4.5 value comes from - Count of Trade =9 from filter Location-Vessel = South and Direct/InDirect = Direct, then divide for number of weeks which in my sample is 2 Weeks
•  9/2 = 4.5 

NOTE: I have a data from week 1 to current week, so the Denominator will change base from previous week which is 34

2st Column Desired output - Ave Direct/InDirect/Trade BP's/Location-Vessel 

• Same on 1st column desired output just additional filter on Column Trade BP's

DESIRED OUTPUT

Trade BP's	Location-Vessel	Trade	Week No:	Direct/InDirect	Ave Direct/InDirect/Location-Vessel	Ave Direct/InDirect/Trade BP's/Location-Vessel
Mech	South	Rigger	33	Direct	4.5	1.5
Civil	South	Plumber	33	In Direct	4	2
Elec	South	Electrician	33	In Direct	4	1
Elec	North	Electrician	33	Direct	6	2
Elec	North	Electrician	33	In Direct	4	1
Elec	North	Electrician	33	Direct	6	2
Civil	North	Plumber	33	In Direct	4	2
Civil	East	Plumber	33	Direct	4	3
Civil	East	Plumber	33	Direct	4	3
Civil	East	Plumber	33	Direct	4	3
Civil	East	Plumber	33	In Direct	3	1
Civil	East	Plumber	33	Direct	4	3
Civil	North	Plumber	33	Direct	6	1.5
Mech	North	Rigger	33	In Direct	4	1.5
Mech	North	Rigger	33	In Direct	4	1.5
Mech	North	Rigger	33	Direct	6	2.5
Mech	North	Rigger	33	Direct	6	2.5
Civil	North	Plumber	33	In Direct	4	2
Civil	South	Plumber	33	Direct	4.5	1
Civil	South	Plumber	33	In Direct	4	2
Civil	South	Plumber	34	In Direct	4	2
Elec	South	Electrician	34	Direct	4.5	2
Elec	South	Electrician	34	In Direct	4	1
Elec	South	Electrician	34	Direct	4.5	2
Elec	East	Electrician	34	In Direct	3	3
Elec	East	Electrician	34	In Direct	3	3
Elec	East	Electrician	34	In Direct	3	3
Civil	East	Plumber	34	Direct	4	3
Civil	East	Plumber	34	Direct	4	3
Civil	South	Plumber	34	In Direct	4	2
Mech	South	Rigger	34	In Direct	4	2
Mech	South	Rigger	34	Direct	4.5	1.5
Mech	South	Rigger	34	In Direct	4	2
Mech	South	Rigger	34	Direct	4.5	1.5
Civil	North	Plumber	34	In Direct	4	2
Civil	North	Plumber	34	Direct	6	1.5
Civil	North	Plumber	34	Direct	6	1.5
Civil	North	Plumber	34	In Direct	4	2
Mech	North	Rigger	34	In Direct	4	1.5
Mech	East	Rigger	34	Direct	4	1.5
Mech	East	Rigger	34	In Direct	3	2
Mech	East	Rigger	34	Direct	4	1.5
Mech	East	Rigger	34	In Direct	3	2
Mech	East	Rigger	33	Direct	4	1.5
Elec	North	Electrician	34	Direct	6	2
Elec	North	Electrician	34	Direct	6	2
Mech	North	Rigger	34	Direct	6	2.5
Mech	North	Rigger	34	Direct	6	2.5
Mech	North	Rigger	34	Direct	6	2.5
Civil	South	Plumber	34	Direct	4.5	1
Elec	South	Electrician	33	Direct	4.5	2
Elec	South	Electrician	33	Direct	4.5	2

Thank you

[v-sgandrathi]

Hi @AllanBerces,

Thank you @danextian , @FarhanJeelani and @grazitti_sapna for your response. 

Has your issue been resolved?
If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Thank you for your understanding!

[FarhanJeelani · Accepted solution]

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.

[AllanBerces]

Hi @FarhanJeelani @danextian @grazitti_sapna @v-sgandrathi thank you very much for the reply, working as i need.

[danextian]

Following your logic used for South, why is east 4 and 4.5? There are nine rows and two weeks so that should be 4.5

I used this calc column to achieve 4.5

Col1 =
VAR _loc = 'Table'[Location-Vessel]
VAR _dir = 'Table'[Direct/InDirect]
VAR _tbl =
    FILTER (
        'Table',
        'Table'[Location-Vessel] = _loc
            && 'Table'[Direct/InDirect] = _dir
    )
VAR _rows =
    COUNTROWS ( _tbl )
VAR _wks =
    COUNTROWS ( SUMMARIZE ( _tbl, 'Table'[Week No:] ) )
RETURN
    DIVIDE ( _rows, _wks )

[grazitti_sapna]

Hi @AllanBerces,

Firstly we need to count distinct weeks

Total Weeks =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Week No:] ),
REMOVEFILTERS ( 'Table'[Direct/InDirect] )
)

Now you can use above measure to calculate Average per Direct/InDirect/Location-Vessel

Ave Direct/InDirect/Location-Vessel =
DIVIDE (
COUNTROWS ( 'Table' ),
[Total Weeks]
)

Average per Direct/InDirect/Trade BP’s/Location-Vessel

Ave Direct/InDirect/TradeBP/Location-Vessel =
DIVIDE (
COUNTROWS ( 'Table' ),
[Total Weeks]
)

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[AllanBerces]

Hi @grazitti_sapna thank you very much for the reply, but the result not correct