Forum Discussion
Measure to calculate Average
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.
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 * _WeekHoursPotential 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 * _WeekHoursFinal 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.
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 LearnHow 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.
3 Replies
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 * _WeekHoursPotential 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 * _WeekHoursFinal 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.
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.
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 LearnHow 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.
