Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Divide column based on other two columns

Hi guys,   I have this table below, the only in my model. As you can see, the Volume (CY_B_Vol) only shows in the first week of each month.   I would like then to be split in weeks according to h...
  • Parisha's avatar
    Parisha
    6 years ago

    Hi , Try below

     

    Firstly create a calculated column with below dax for filling all the rows  Column 2 = var cur='Table'[monthofyear] return if('Table'[monthofyear]=cur,(calculate(sum('Table'[cy-b-col]),filter('Table','Table'[monthofyear]=cur))))

     

    Second Create an calculated TABLE with below dax and create a relationship between main table and this table with [monthofyear] Output = SUMMARIZE ( 'Table','Table'[monthofyear], "Count value", COUNT ( 'Table'[monthofyear] ) )

     

    Now final create a calculated column as below CalculatedColumn = DIVIDE('Table'[Column 2],if(RELATED(Output[Count value])=5,20,25))

     

    ..Its a bit lengthy but will serve your purpose...

Parisha's avatar
Parisha
Microsoft Employee
6 years ago

Hi , Try below

 

Firstly create a calculated column with below dax for filling all the rows  Column 2 = var cur='Table'[monthofyear] return if('Table'[monthofyear]=cur,(calculate(sum('Table'[cy-b-col]),filter('Table','Table'[monthofyear]=cur))))

 

Second Create an calculated TABLE with below dax and create a relationship between main table and this table with [monthofyear] Output = SUMMARIZE ( 'Table','Table'[monthofyear], "Count value", COUNT ( 'Table'[monthofyear] ) )

 

Now final create a calculated column as below CalculatedColumn = DIVIDE('Table'[Column 2],if(RELATED(Output[Count value])=5,20,25))

 

..Its a bit lengthy but will serve your purpose...