Totals for Growth indicator

[sergiy razumov]

Hi, everybody!<o></o>
I’m quite new to DAX and have a situation that I don’t know howto handle.<o></o>
I’ve a measure that calculates growth of Students enrollmentcomparing to the previous year:

<o></o>

mGrowthAccepted[CoE]:=IF(ISBLANK([mCostOfCampaighn]),BLANK(),<o:p></o:p>
[B]                [mStud(Accepted[CoE]])]-[mStud(Accepted[CoE]]_LY)]<o:p></o:p>[/B]
)

It works just fine if data is in context of years. But on GrandTotals I have zeros (there is no previous years for Totals).
<o></o>
Can somebody suggest please how this formula can be modifiedto show for Grand Totals the sum of individual years’ growth, so that in GT wehave growth of the students for all the years? That sounds like iteration overthe years, yet I have no idea, how to manage this situation in DAX.<o></o>

 

[Matt Allington]

There are many nuances that you will discover as you learn DAX. This is one of them. In the pre-self service world, the IT guys would have sorted this out and you would not have to deal with it. This is why it can be challenging for Excel users.

In this scenario there is no valid answer at the grand total level, because growth ONLY makes sense in the context of a single year. Every time there is more than one year in your data (as in a grand total) there is no valid answer. One approach is to wrap your formula in another if statement

=if(hasonevalue(calendar[year],YOURMEASURE)

this will return a blank anywhere there is more than 1 year present.

you will probably learn faster and with less pain with my book if you are interested. Http://Xbi.com.au/learndax