Power BI Matrix Per Hour Overview

[Sana-Shaikh]

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?

[v-yilong-msft · Accepted solution]

Hi @Sana-Shaikh ,

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.

[Sana-Shaikh]

No, This gives me the same percentage error. I think time needs to be added in the DAX calculation. The formula is counting every percentage instead of every cumulative rider divided by total capacity

[v-yilong-msft · Accepted solution]

Hi @Sana-Shaikh ,

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.

[v-yilong-msft · Accepted solution]

Hi @Sana-Shaikh ,

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.