Forum Discussion
Power BI Matrix Per Hour Overview
I have 2 matrices I need to create:
1. Matrix showing Utilization % per Ride per weekday
2. Matrix showing Total Utilization % per hour per weeekday
The ride data is in the Ridership Events table and the Capacity data is in the Ride table.
I created a calculation for the first Matrix and it works with the data:
The problem I am having is with the second Matrix per hour. When I click on the first matrix for a specific ride, then the second matrix changes accordingly to the correct values per hour when it was
Util% = DIVIDE(SUM('RidershipEvents'[CumulativeRiders]), SUM('Ride'[Capacity]), 0)
utilized.
The problem lies in the overview of the second Matrix. When I open the Power BI file with no filters placed, the data is showing percentages well over 100%. I believe the Matrix is calculating the total percentages per hour, for example: (50% + 30% + 80% = 160%)
Instead, it should be calculating (Total Cumulative Riders/ Total Capacity), for example: (350/400 = 87.5%
I think the second matrix needs a different DAX calculation for Utilization % that takes total riders per hour and divides total possible riders per hour.
Can anyone help with this issue?
Hi Anonymous ,
I think you can use the VAR function to write the variables one by one, here is the DAX code you can try.
Total Utilization % = VAR TotalRidersPerHour = SUM ( 'RidershipEvents'[CumulativeRiders] ) VAR TotalCapacityPerHour = SUM ( 'Ride'[Capacity] ) RETURN DIVIDE ( TotalRidersPerHour, TotalCapacityPerHour, 0 )If this code doesn't work you can provide me more information or your .pbix file.
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
I think I'm missing your point probably because I don't have a complete view of the data in your table. If you can provide me with specific data from the table, I will look into your question further.
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi Anonymous ,
I think you can use the VAR function to write the variables one by one, here is the DAX code you can try.
Total Utilization % = VAR TotalRidersPerHour = SUM ( 'RidershipEvents'[CumulativeRiders] ) VAR TotalCapacityPerHour = SUM ( 'Ride'[Capacity] ) RETURN DIVIDE ( TotalRidersPerHour, TotalCapacityPerHour, 0 )If this code doesn't work you can provide me more information or your .pbix file.
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
I think I'm missing your point probably because I don't have a complete view of the data in your table. If you can provide me with specific data from the table, I will look into your question further.
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.