Forum Discussion
Dividing within the same column
Hi,
I have a table with 4 columns
ID Factor Component Quantity
name1 1 A 0,5
name1 0 B 2
name1 1 C 7
name1 1 Total 12
name2 1 A 0,7
name2 0 B 2
name2 1 Total 14
I want to calculate new column with the percentege of component (A,B,C...) in Total for each ID. I only want to calculate the percentage for row where factor = 1 (show 0,0 or null for Factor 0 rows). I achived this by multiplying quantity by factor before percentage calculation.
I know how to use FILTER function but cannot work out a working solution.
davidz106 UPDATED - check this out.
Percentage_ = VAR DValue = CALCULATE(SUM('Divide'[Quantity]),FILTER(ALLEXCEPT('Divide','Divide'[ID]),'Divide'[Component] ="D")) RETURN IF(SUM('Divide'[Factor]) = 1,SUM('Divide'[Quantity])/DValue,0)Accept the solution if it fulfills your need.
6 Replies
davidz106 Can you show what is the desired output you are expecting.
davidz106 UPDATED - check this out.
Percentage_ = VAR DValue = CALCULATE(SUM('Divide'[Quantity]),FILTER(ALLEXCEPT('Divide','Divide'[ID]),'Divide'[Component] ="D")) RETURN IF(SUM('Divide'[Factor]) = 1,SUM('Divide'[Quantity])/DValue,0)Accept the solution if it fulfills your need.
Thank you very much. This is exactly what I was looking for.
I would expect a result like this:
ID Factor Component Quantity Percentage
name1 1 A 0,5 4,2
name1 0 B 2 null or 0
name1 1 C 7 58,3
name1 1 Total 12 100,0
name2 1 A 0,7 5,0
name2 0 B 2 null or 0
name2 1 Total 14 100,0
