Average

[SantiagoTorres]

Hi All, hope you're well,

Can you please help me with this? I'm trying to get the average site/visits per company, and I got the following measures.

Site  Visits = COUNTROWS(SUMMARIZE('3. CoW Productivity',DimProject[Project Number],'Calendar'[Date], DimCoWInsp[CoWInsp]))

Workdays = COUNTROWS(SUMMARIZE('3. CoW Productivity','Calendar'[Date]))

Site Visits/day =  DIVIDE( [Site  Visits] , [Workdays])

CoWComp	CoWInsp	Site  Visits	Workdays	Site Visits/day
Company 1	Inspector 1	151	40	3.78
Company 1	Inspector 2	1494	429	3.48
Company 1	Inspector 3	44	13	3.38
Company 1	Inspector 4	60	18	3.33
Company 1	Inspector 5	1360	414	3.29
Company 1	Inspector 6	61	19	3.21
Company 1	Inspector 7	1144	369	3.10
Company 1	Inspector 8	1188	384	3.09
Company 2	Inspector 9	805	272	2.96
Company 1	Inspector 10	32	11	2.91
Company 3	Inspector 11	227	80	2.84
Company 1	Inspector 12	17	6	2.83
Company 2	Inspector 13	727	286	2.54
Company 2	Inspector 14	246	97	2.54
Company 3	Inspector 15	476	199	2.39
Company 3	Inspector 16	40	17	2.35
Company 2	Inspector 17	630	280	2.25
Company 3	Inspector 18	28	14	2.00
Company 2	Inspector 19	198	118	1.68
Company 2	Inspector 20	166	102	1.63
Company 3	Inspector 21	6	4	1.50
Company 2	Inspector 22	47	32	1.47
Company 2	Inspector 22	157	110	1.43
Company 3	Inspector 23	47	35	1.34
Company 3	Inspector 24	56	47	1.19
Company 2	Inspector 25	4	4	1.00
Company 2	Inspector 26	6	6	1.00
		9417	493	19.10

I need to get to these average site visits/day values calculated in Excel.

Company 1	3.24
Company 2	1.85
Company 3	1.95
	Avg	2.34

Thanks as always,

Sanitago Torres

[Ashish_Mathur]

Hi,

Share the download link of the PBI file and show the expected result there.

[SantiagoTorres]

Hi Ashish @Ashish_Mathur , hope you're well. Sorry for the late reply,

I'm kind of new to interacting here, and I dont know how to provide the information you're requesting. I couldn't find a way to load a file. Regarding the expected results, please refer to my first post; I believe I provided all the information there.

Thanks for your help,

[Ashish_Mathur]

Hi,

Upload the file to Google Drive and share the download link here.  Show the expected result clearly.

[SantiagoTorres]

Hi @Ashish_Mathur, thanks for your patience. Please see below the data with formulas in Excel with the results expected to get in PBI.

Data for Avg in Excel 

[Ashish_Mathur]

Hi,

Share the download link of the PBI with the measures already there.  Clearly show the problem there and the expected result. 

[ppm1]

Please try this measure expression. Replace T4 with your actual table name.

Avg Measure =
AVERAGEX (
    VALUES ( T4[CoWComp] ),
    CALCULATE ( AVERAGEX ( t4, T4[Site  Visits] / T4[Workdays] ) )
)

Pat

[SantiagoTorres]

Hi Pat, thanks for your response,

When you get a gap, can you please tell me if Im doing something wrong here,

Avg Site Visits/Day = AVERAGEX( VALUES( DimCoWCom[CoWComp] ),

                                      CALCULATE( AVERAGEX('2. Observations', [Site  Visits] / [Workdays] ) ) )

These are the results I'm getting, which are not correct.

CoWComp	Avg Site Visits/Day
Company 1	1
Company 2	1
Company 3	1

Thanks for your time,

Santiago Torres

[SantiagoTorres]

Hi Pat @ppm1 , thanks for your response,

When you get a gap, can you please tell me if Im doing something wrong here,

Avg Site Visits/Day = AVERAGEX( VALUES( DimCoWCom[CoWComp] ),

                                      CALCULATE( AVERAGEX('2. Observations', [Site  Visits] / [Workdays] ) ) )

These are the results I'm getting, which are not correct.

CoWComp	Avg Site Visits/Day
Company 1	1
Company 2	1
Company 3	1

Thanks for your time,

Santiago Torres

[ppm1]

Looks like you are referencing measures in your inner AVERAGEX, while I used columns. Please switch to column references and it should work.

Pat

[SantiagoTorres]

Hi Pat @ppm1, that's correct. Can you please elaborate a bit more? As you can see in my first post, these two DAX measures are coming from the data I got from previous years (massive table), so I dont have two custom columns. Hope this makes sense to you.

Site  Visits = COUNTROWS(SUMMARIZE('3. CoW Productivity',DimProject[Project Number],'Calendar'[Date], DimCoWInsp[CoWInsp]))

Workdays = COUNTROWS(SUMMARIZE('3. CoW Productivity','Calendar'[Date]))

Thanks,

[ppm1 · Accepted solution]

Please try this one instead.

Avg Measure =
AVERAGEX (
    VALUES ( T4[CoWComp] ),
    [Site Visits/day]
)

Pat