Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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.
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 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.
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.
User | Count |
---|---|
48 | |
44 | |
19 | |
14 | |
14 |
User | Count |
---|---|
107 | |
55 | |
28 | |
20 | |
15 |