Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
davidz106
Helper II
Helper II

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. 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@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)

 

 

 

Mohan1029_0-1658337401165.png

 

Accept the solution if it fulfills your need.

 

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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

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
Not applicable

@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)

 

 

 

Mohan1029_0-1658337401165.png

 

Accept the solution if it fulfills your need.

 

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. 

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_0-1658349253842.png

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.