Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Subtotal / SUMIF in table using DAX

Hello,   I'm struggling with DAX formula. This is very easy to achieve using Excel, but I do not have idea how to do this using DAX. Simply, I need a Column (yellow one in example below) which for...
Need Help
  • v-xicai's avatar
    v-xicai
    7 years ago

    Hi mat_k,

     

    According to your description, I create sample data to reproduce the scenario. You can create column named Sum of Y for Dept and Month to meet your demand.

     

    Sum of Y for Dept and Month = CALCULATE(SUM(Table1[Value]),FILTER(Table1,Table1[Department]=EARLIER(Table1[Department])&&Table1[Month]=EARLIER(Table1[Month])&&Table1[Criteria]="y"))

     

    Choose table visual to display the result showing picture below.

     

     

     

     

     

     

     

     

     

     

     

     

    Here is my test pbix file link: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/Ef7WV4uL90RCiNG-gtoCE1wBSy_onAKrx4uxW7MtU6Jn7Q?e=Ncqq3d

     

    Best Regards,

    Amy

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-xicai's avatar
v-xicai
Icon for Community Support rankCommunity Support
7 years ago

Hi mat_k,

 

According to your description, I create sample data to reproduce the scenario. You can create column named Sum of Y for Dept and Month to meet your demand.

 

Sum of Y for Dept and Month = CALCULATE(SUM(Table1[Value]),FILTER(Table1,Table1[Department]=EARLIER(Table1[Department])&&Table1[Month]=EARLIER(Table1[Month])&&Table1[Criteria]="y"))

 

Choose table visual to display the result showing picture below.

 

 

 

 

 

 

 

 

 

 

 

 

Here is my test pbix file link: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/Ef7WV4uL90RCiNG-gtoCE1wBSy_onAKrx4uxW7MtU6Jn7Q?e=Ncqq3d

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thanks!