Forum Discussion
SUMIF / DAX
Hi,
I am new to DAX but I am struggling with a very simple question.
I have the following table:
| AccountingPeriod | Project | Amount |
| 201801 | X | 10 |
| 201802 | X | -10 |
| 201803 | 20 | |
| 201803 | Y | 50 |
| 201803 | Y | 0 |
What I want is to add a measure which calculate the total a project (over the entire selection). So the output should be like this:
| AccountingPeriod | Project | Amount | Project Total |
| 201801 | X | 10 | 0 |
| 201802 | X | -10 | 0 |
| 201803 | 20 | 20 | |
| 201803 | Y | 50 | 50 |
| 201803 | Y | 0 | 50 |
I can't seem to get it working! Can anybody help?
8 Replies
Frankly, the output you want is a bit misleading to the viewer. What does each line represent? You could try doing a matrix instead of a table, and set your rows to have a hierarchy of Project => Accounting Period. If you do this, you'll see the amount for each accounting period after a single drilldown, as well as row subtotals that will give you the data you're after. I'm assuming the last accounting periods are supposed to be different, or those are otherwise individual line items that you can delineate with an identifier.
Thanks for the reply. I can understand your comment, that it can be misleading, however I want to use the measure to filter out all the enrties for which the projects have a 0 balance. The matrix solution unfortunately is not possible, as the user want to see all detailed lines (transactions) at once, instead of drilling up and down.
In that case, you can leverage the interactivity of Power BI. On one table or chart (for example a bar chart), you have a listing of projects with their aggregate balance. This can be done easily out of the box, and you can filter that visual to have a sum of Amount greater than zero, or not equal to zero. Once the user clicks on any projects there, a table showing the individual transactions will be filtered to that project.
If you really need to, you can use CALCULATE with a simple SUM on Amount, and a filter that compares "Project" to "EARLIER(Project)".
Thanks for the response however I get the following error when using the formula:
EARLIER/EARLIEST refers to an earlier row context which doesn't exist.
How can I solve this / Am I doing something wrong?
Here is my formula with the table names:
PROJ_TOTAL = CALCULATE(SUM('ENT_SCD rptGLBalance'[Amount]);FILTER('ENT_SCD rptGLBalance';'ENT_SCD rptGLBalance'[Project]=EARLIER('ENT_SCD rptGLBalance'[Project])))
brotha_lee wrote:Thanks for the response however I get the following error when using the formula:
EARLIER/EARLIEST refers to an earlier row context which doesn't exist.
How can I solve this / Am I doing something wrong?
Here is my formula with the table names:
PROJ_TOTAL = CALCULATE(SUM('ENT_SCD rptGLBalance'[Amount]);FILTER('ENT_SCD rptGLBalance';'ENT_SCD rptGLBalance'[Project]=EARLIER('ENT_SCD rptGLBalance'[Project])))
Create as a calculated column, not as measure. You will not get the error.Thanks
Raj
