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

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?

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi JarroVGIT , tks again 😃

 

Does your current model look like the table you gave us but without the yellow column? Correct! The yellow column is exactly what I want.
Or did I understand your question wrong? I assumed you have the white columns and you wanted the yellow column. Correct again
Or are you missing the weeknumbers as well? No, I'm not.

 

As you can notice, I'm a newbie. I don't know if my logic is good, but I thought maybe get the minimum value for the week (where the month is equal the selected one and divide (with if based on the month as well); however, I'm still trying to accomplish this.

 

I really appreciate your patience and attention 😃

  • JarroVGIT's avatar
    JarroVGIT
    Resident Rockstar
    6 years ago
    Alright then my initial response gets you that yellow column. If you want the columns to be in there original state, please create a copy of column CY_B_Vol before using the Fill option in the Powerquery editor.
    • JarroVGIT's avatar
      JarroVGIT
      Resident Rockstar
      6 years ago
      Or paste your PBIX file and I'll show it to you
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi JarroVGIT , unfortunately not. =/

       

      Below is what I get in my file doing exactly what you said.

       

      • JarroVGIT's avatar
        JarroVGIT
        Resident Rockstar
        6 years ago
        Can you share your pbix file by sharing a Dropbox/Google drive/OneDrive link? Your results are not in line with documented power bi behavior so the error must be somewhere else.