Forum Discussion
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 specific Department and Month will show sum of values wich have "y" in criteria column. So, even if there is "x" in specific row, it will show the sum of falues for "y".
How to achieve this?
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.
2 Replies
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.