Percent between 2 columns

[Kelsey024]

Hello,

I am having some trouble with calculating a percentage between 2 values in 2 different columns. I want to get the percentage of gallons consumed by the water target for that specific location. The '% of Target Used' is the column I want.

This is what I want (in Excel):

How do I get the same to work in Power BI? 

Everytime I try to divide the consumption by the water target I am getting an incorrect percentage. 

If anyone has a quick solution to this problem or why I am having such a hard time in PBI, I would very much appreciate it!

Thank you!

[v-yuta-msft · Accepted solution]

Hi Kelsey024,

Create a measure in your table using DAX below:

% of Water Target Used =
DIVIDE ( [Consumption], [WATER TARGET] )

Then change the format of [% of Water Target Used] to percentage.

Regards,

Jimmy Tao

[Anonymous]

Now I am getting this error:
"A single value for column 'Staff Count per Week' in table 'AggData' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

My data is similar to the water usage data above where one column is the number of people in an office vs the number of employees assigned to that office.

The DAX I'm trying is 

% of Office Used = DIVIDE(AggData[Staff Count per Week], [Employees Office] )

AggData is the table

So this data does change throughout the columns so not sure how to fix this and get the desired result.

Any suggestions or help is appreciated.