get a column calculating the ratio of a category

[zzzsharepoint]

I have table like this 

Project	Category	Value
A	BUZ	2
A	JUZ	8
A	JUZA	7
A	OUZ1	21
B	KJUZ	12
B	JUZ	211

I need to calculate ratio for each category So for example for Project A Category BUZ ratio will be (2/(2+8+7+21)) and for category JUZ it will be (8/(2+8+7+21))

For Project B it will be 12/(12+211) for category KJUZ.

Project	Category	Value	Ratio
A	BUZ	2	0.0526
A	JUZ	8	0.2105
A	JUZA	7	0.184
A	OUZ1	21	0.55263
B	KJUZ	12	0.053811
B	JUZ	211	0.946

How can I get it? I need to have it as a column in my Power Bi table.

[FreemanZ · Accepted solution]

hi @zzzsharepoint 

try to create a new column with this

ValueRatio =

VAR ProjectValue =

CALCULATE(

    SUM(TableName[Value]),

    ALLEXCEPT(TableName, TableName[Project])

)

RETURN FORMAT(DIVIDE(TableName[Value], ProjectValue), "0.0%")

I tried and it worked like this:

[Uspace87]

@zzzsharepoint 

Hi, 

You can create the following measures:

1) Total by project = CALCULATE( SUM('Table'[Value]),REMOVEFILTERS('Table'[Category]))
2) Ratio = DIVIDE(SUM('Table'[Value]),[Total by project])