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 how many weeks we have in a month (if the month has 5 weeks, split (CY_B_Vol) by 20 if the month has 4 weeks split (CY_B_Vol) by 25), like in the yellow column.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

It seems like a simple thing to do, but I've been struggling with this for 2 days without a solution. ☹️

 

Could anybody help me, please?

 

Thanks!

  • 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...

9 Replies

  • JarroVGIT's avatar
    JarroVGIT
    Resident Rockstar
    6 years ago

    In Power Query Editor screen, select column CY_B_VOL and click on Fill (Down) in the Transform tab:

    Then click Close & Apply and add a calculated column to your table:

    CalculatedColumn = 
VAR _curMonth = Table[Month_of_Year]
RETURN
DIVIDE(Table[CY_B_Vol], COUNTROWS(FILTER(ALL(Table), Table[Month_of_Year] = _curMonth)))

    Let me know if this works 🙂

     

    Kind regards

    Djerro123

    -------------------------------

    If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

    Keep those thumbs up coming! 🙂

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi JarroVGIT, thanks for your quick reply.

      It actually didn't work. =/

      First, the volume should not fill down, as I want the count to be done with the first value only.

      Second, I need to divide the volume that appears in the first week of each month.

      If the month is equal to 3,6,9 or 12, I would divide by 20, the others I would divide by 25. 

      Anyhow, I appreciate your help 😃

      • JarroVGIT's avatar
        JarroVGIT
        Resident Rockstar
        6 years ago

        Does your current model look like the table you gave us but without the yellow column? Or did I understand your question wrong? I assumed you have the white columns and you wanted the yellow column. Or are you missing the weeknumbers as well?

  • 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...