Measure to calculate Average

[dataanalyst2023]

Hi all, 

I have 2 tables as shown below. I'm trying to write a DAX to get the Average of the "Actual Rate" and "Potential Rate". the reason why I have two tables is the raw data for the first table is by date as for the second table is just by location. 

Table 1: 

Table 2:

This is how I have it written in excel, I can't seem to be able to get the same results in Power Bi. 

Actual Rate = ([AVG(Actual Index)]*[5 Week Incident Count]/1000)*200000/[5 Week Hours]

Potential Rate = ([AVG(Potential Index)]*[5 Week Incident Count]/1000)*200000/[5 Week Hours]

Thank you in advanced. 

[Anonymous · Accepted solution]

Hi @dataanalyst2023 ,

Depending on the information you have provided, you can follow these steps below:

1.Merge Queries.

2.Add new columns.

Actual Rate =
VAR _AverageActual =
    AVERAGE ( 'Table 1'[Average Actual Index] )
VAR _WeekIncidentCount = 'Table 1'[Table 2_5 week Incident Count] / 1000
VAR _WeekHours = 200000 / 'Table 1'[Table 2_5 Week worked Hours]
RETURN
    _AverageActual * _WeekIncidentCount * _WeekHours

Potential Rate =
VAR _AveragePotential =
    AVERAGE ( 'Table 1'[Average Potential Index] )
VAR _WeekIncidentCount = 'Table 1'[Table 2_5 week Incident Count] / 1000
VAR _WeekHours = 200000 / 'Table 1'[Table 2_5 Week worked Hours]
RETURN
    _AveragePotential * _WeekIncidentCount * _WeekHours

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dataanalyst2023]

Thank you for your help. Its almost working. 

Can I add multiplication between these two lines

and these two?

these are the results I'm getting 

these are what I need 

Thank you so much for your help again. 

[Anonymous · Accepted solution]

Hi @dataanalyst2023 ,

You can certainly add multiplication between two lines, and you can check out the documentation below to see how the VAR function is used, which can help you understand it better:
Use variables to improve your DAX formulas - DAX | Microsoft Learn

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @dataanalyst2023 ,

Depending on the information you have provided, you can follow these steps below:

1.Merge Queries.

2.Add new columns.

Actual Rate =
VAR _AverageActual =
    AVERAGE ( 'Table 1'[Average Actual Index] )
VAR _WeekIncidentCount = 'Table 1'[Table 2_5 week Incident Count] / 1000
VAR _WeekHours = 200000 / 'Table 1'[Table 2_5 Week worked Hours]
RETURN
    _AverageActual * _WeekIncidentCount * _WeekHours

Potential Rate =
VAR _AveragePotential =
    AVERAGE ( 'Table 1'[Average Potential Index] )
VAR _WeekIncidentCount = 'Table 1'[Table 2_5 week Incident Count] / 1000
VAR _WeekHours = 200000 / 'Table 1'[Table 2_5 Week worked Hours]
RETURN
    _AveragePotential * _WeekIncidentCount * _WeekHours

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.