PowerPivot and DAX papers,posts,FAQs,webcasts,samples

I have a bar chart.
That shows in Axis Fields the year and month.

Each month has a value associated: Dec/2007 - 95%, Jan/2008 - 98%, Fev/2008 - 95%.. and so on.

That is ok.

But I need to plot a line into this same graph that is an accumulated for each month in that year.

Jan = Jan / 1
Feb = (Jan + Feb) / 2
Mar = (Jan + Feb + Mar) / 3
...
Dec = (Jan + Feb + ... + Dec) / 12

Is there an easy way to to that?

There is attached a graph that is similar to that i have to build (please ignore "S1, S2..." part).

--

There is another question:
I've created a time dimension, and I'm using Year/Month from that table in the Axis Field. But if there's no data in the fact, that months doesn't appear. How can I show all months (like in the image)?



Ideas are very welcome!
Thanks!

Now the image is attached



Or you can view it here:
zavaschi.com/wp-content/uploads/2010/06/DSC00063.jpg


Thanks!

Hi,

Lets start with the second part of your question:

To show month when there is no fact data for these months, you have to change pivot table/ pivot chart display option:

- Anywere in related Pivot table right mouse click, then choose "PivotTable Options"
- go to tab "Display"
- Check checkbox "Show items with no data on rows" or "Show items with no data on columns".

PivotChart has somewere related pivot table, so that is where you would change your settings.

Now first part.

Lets say we have Table1 with following values:

Year Month Value
2007 1 80%
2007 2 81%
2007 3 82%
2007 4 83%
2007 5 84%
2007 6 85%
2007 7 86%
2007 8 87%
2007 9 88%
2007 10 89%
2007 11 90%
2007 12 91%
2008 1 92%
2008 2 93%
2008 3 94%

We create PowerPivot based on that table, then we will create pivot and add following calculations:

AccumSum:
=IF(COUNTROWS(VALUES(Table1[Month])) <> 1, BLANK(), CALCULATE(SUMX(Table1, Table1[Value]), Table1[Month] <= VALUES(Table1[Month])))

MonthToDateCount:
=IF(COUNTROWS(VALUES(Table1[Month])) <> 1, BLANK(), CALCULATE(COUNTROWS(DISTINCT(Table1[Month])), Table1[Month] <= VALUES(Table1[Month])))

AvgAccumValue:
=IF([MonthToDateCount] = 0, Blank(), [AccumSum] / [MonthToDateCount])

That last value AvgAccumValue is what you are looking for.
For above example, results would be:

Row Labels Sum of Value AccumSum MonthToDateCount AvgAccumValue
2007 10.26
1 0.8 0.8 1 0.8
2 0.81 1.61 2 0.805
3 0.82 2.43 3 0.81
4 0.83 3.26 4 0.815
5 0.84 4.1 5 0.82
6 0.85 4.95 6 0.825
7 0.86 5.81 7 0.83
8 0.87 6.68 8 0.835
9 0.88 7.56 9 0.84
10 0.89 8.45 10 0.845
11 0.9 9.35 11 0.85
12 0.91 10.26 12 0.855
2008 2.79
1 0.92 0.92 1 0.92
2 0.93 1.85 2 0.925
3 0.94 2.79 3 0.93
Grand Total 13.05

As you can see in my sample data I used month numbers and not names, because I needed month ordering. If you would like to use names, try using names something like:
01-Jan
02-Feb
03-Mar

Vidas, just a suggestion - if there is a Date dimension it should be better to avoid this syntax:

CALCULATE(COUNTROWS(DISTINCT(Table1[Month])), Table1[Month] <= VALUES(Table1[Month]))


In favor of this one:

CALCULATE( COUNTROWS( VALUES( Table1[Month] ) ),
DATESYTD( Dates[Date] ) )

Of course, you would need a Dates table...

Marco

Hi Marco,

As I wrote my initial DAX statements, I assumed that there is no date column/table and everything is at month level.

After that I had few e-mail exchanges with Zavaschi. When I got his sample structure, I found that there was a date table. Also I found that:
- he wants to use calculated measure in this calculation
- that calculated measure is "ratio" measure where he sums something over month (x) and divides by count of events in that month (y). So formula for this calculated measure [%s] is something like x/y
- because he requires to calculate ratio at the month level, I cannot use any YTD functions.

To be honest I struggled for a while with this request, because I kept getting back incorrect results. Again, issue is that he wants to sum x to month level, then sum y to month level, calculate x/y and then sum that result to YTD and divide by month count.

I tried using:


But that just somes everything at day level and result is not what I expected.

Finally I wrote following formulas:


AccumSum:


MonthCount:


And final measure you are looking for (calculated just for month where [%s] is not 0) :



These formulas were producing correct results.

Thank you for clarification, Vidas!

Marco