How to divide a measure by a column value?

[Anonymous]

I am trying to calculate the cube utilization % by Route and Month & Year. I created measures called Total Cube, Day Count, and Avg Used Cube/Day.   

Total Cube = CALCULATE(SUM('Total Cube'[Total Cube]))

Day Count = DISTINCTCOUNT('Total Cube'[Date])

Avg Used Cube/Day = DIVIDE([Total Cube],[Day Count])

I want to divide the Avg Used Cube/Day by the Usable Capacity (which comes from the below Truck Specs table). I want the Usable Capacity value that returns as the denominator to be dynamic based on the route. Meaning, Route FL2 returns 872 and Route LA1 returns 701.

Any help is appreciated.

[v-cherch-msft]

Hi @Anonymous

You may try below measure.Change 'Table' to the actual table name.If it is not your case,please share your sample data which could reproduce your scenario and your desired output.You can upload the .pbix file to OneDrive and post the link here. Do mask sensitive data before uploading.

Measure =
DIVIDE ( [Avg Used Cube/Day], CALCULATE ( SUM ( Table[Usable Capacity] ) ) )

Regards,

Cherie

[Anonymous]

Hello @v-cherch-msft,

I appreciate the response. I used your formula in the measure and it gave me an incorrect (lower than expected) % for utilization. I have attached a screenshot of the results with the cube utilization listed in the far right column. I need the end result in column Cube Utilization to be Avg Used Cube/Day divided by Usable Capacity. For example, row 1 for Dec 2017 and LA1 would be 37.15% rather than 12.38%.

 

Per your request, I have uploaded the .pbix file to folder Power BI and the link is below.

https://gtvault-my.sharepoint.com/:u:/r/personal/jdaly35_gatech_edu/Documents/Power%20BI/Cube%20Dash...

Sincerely,

Jack

[v-cherch-msft]

Hi  @Anonymous

I cannot access the file from sharepoint.Could you share it with onedrive?

Regards,

Cherie

[Anonymous]

@v-cherch-msft,

Sorry about that. My OneDrive account was frozen from lack of use. Let me know if the below link works for you.

https://1drv.ms/u/s!AiFttrDOn_FqfXiOTPIJunpUOVg

Sincerely,

Jack

[v-cherch-msft · Accepted solution]

Hi @Anonymous

I would suggest you change the relationship as below.Then you may get the expected output with above measure.Attached the sample file.

Regards,

Cherie

[Anonymous]

@v-cherch-msft

Thank you very much! This obtains the output I was hoping for. Would you mind explaining the reasoning behind changing the cross-filter direction from single to both for the Truck Specs and Total Cube table relationship?