Hi,
I am trying to calculate the average bill amount for a based on services utilized. I have 3 columns each representing a different service and a total bill amount column containing the total bill amount for the row. I want to know the average bill amount for only individuals whom received services. Therefore, I only want the total bill amount/total quantity of services if >0. I want to integrate these results to a powermap, however, because not every row has a value, I cannot use the average function in powermap as it will provide a sum/total rows. Furthermore some bill amounts are comprised of multiple services so dividing by row count will be inaccurate. Unless there is a way to add custom formulas to powermap, I think this will have to be done in the powerpivot. Is there a way to apply the below formula to every zip code without witting every zip code?
THis is what I have so far
=CALCULATE(SUM('zip codes'[BILL_AMT]),'zip codes'[Total Visits]>0)/CALCULATE(SUM('zip codes'[Total Visits]),'zip codes'[Total Visits]>0)
Also, the 'Total Visits' column is comprised of the 3 service type columns.
Any help would be greatly appreciated.
Thanks
I am trying to calculate the average bill amount for a based on services utilized. I have 3 columns each representing a different service and a total bill amount column containing the total bill amount for the row. I want to know the average bill amount for only individuals whom received services. Therefore, I only want the total bill amount/total quantity of services if >0. I want to integrate these results to a powermap, however, because not every row has a value, I cannot use the average function in powermap as it will provide a sum/total rows. Furthermore some bill amounts are comprised of multiple services so dividing by row count will be inaccurate. Unless there is a way to add custom formulas to powermap, I think this will have to be done in the powerpivot. Is there a way to apply the below formula to every zip code without witting every zip code?
THis is what I have so far
=CALCULATE(SUM('zip codes'[BILL_AMT]),'zip codes'[Total Visits]>0)/CALCULATE(SUM('zip codes'[Total Visits]),'zip codes'[Total Visits]>0)
Also, the 'Total Visits' column is comprised of the 3 service type columns.
Any help would be greatly appreciated.
Thanks
