SUMIF / DAX

[brotha_lee]

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?

[Ashish_Mathur · Accepted solution]

Hi,

Here is the result i got.  You may download the PBI file from here.

Hope this helps.

[Anonymous]

Hi

This will help you.

Proj_total = CALCULATE(SUM(Ex_Over[Amount]), FILTER(Ex_Over,Ex_Over[Project]=EARLIER(Ex_Over[Project])))

Thanks
Raj

[brotha_lee]

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])))

[Anonymous]

---

@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

[brotha_lee]

Thanks that did the trick, however I now have the following issue,. I have a measure which calculates the YTD values based on the following formula:

YTD = round(CALCULATE('sum(ENT_SCD rptGLBalance'[amount]);filter(all('ENT_SCD dimAccountingPeriods'[AccountingPeriodKey]);'ENT_SCD dimAccountingPeriods'[AccountingPeriodKey]<=Max('ENT_SCD dimAccountingPeriods'[AccountingPeriodKey])));0).

when I use this measure in the column like:

PROJ_TOTAL = CALCULATE('ENT_SCD rptGLBalance'[YTD])FILTER('ENT_SCD rptGLBalance';'ENT_SCD rptGLBalance'[Project]=EARLIER('ENT_SCD rptGLBalance'[Project])))

it give me funny results. For example when I select accountingperiod "201802" the result is as follows:

AccountingPeriod	Project	Amount	YTD	PROJ_TOTAL
201801	X	10	10
201802	X	-10	-10	-10

It looks like the PROJ_TOTAL is only calculating for the selected accountingperiod. Can this be solved?

[Anonymous]

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.

[brotha_lee]

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.

[Anonymous]

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