Dividing within the same column

[davidz106]

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. 

[Anonymous]

@davidz106 Can you show what is the desired output you are expecting.

[davidz106]

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

[Anonymous · Accepted 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.

[davidz106]

Is there a way to define this as a new column in M language? I am having some probles with DAX solution due to table relations. 

[davidz106]

Althogh if I define percentage as measure your code works perfect. If I define percentage as column it does not. I find it a bit curios. I do not really need it to work as a column so it's all good.

[davidz106]

Thank you very much. This is exactly what I was looking for.