PowerPivot relationships issues

Question

Hey all,

I have created a PowerPivot model based on:

•Customer
•Date
•Product
•Sales

These four sources of data come from SQL tables. Sales links to Customer, Date and Product.

On the customer tab of PowerPivot I have created a calculation to get the total sales value per customer:

=SUMX(RelatedTable(Sales),Sales[GBP Value Inc Tax])

I then have a second calculated column on the customer tab which calculates the sales band the customer is in.

However when I slice my pivot by date or product, the selections are not observed. I guess this is because there is no relationship between customer and product, or customer and date.

How can I add the ability to filter by date and product?

Many thanks,

Matt

Answer 1

Hi Matt,

Calculated Columns get only evaluated once - after first defining them or during a table process operation - and then the resulting row-by-row values get persisted.  What that implies is that their values are static and do not change based on user selection.   If you need user selection (from a slicer or filter) to be taken into account, you must define your expression as a DAX measure... not a calculated column

Hope that helps

---

Javier Guillen
http://javierguillen.wordpress.com/

Answer 2

Javier,

Thanks for your reply - that makes sense.

I've created a band table (Excel linked table) which has 3 columns, name, min value and max value. Can you explain what my calculation should be so that a user can drop band name on and have the 'band count' be representative of their slicers and selections?

Thank you again,

Matt

Answer 3

Hi Matt,

Have you seen Alberto Ferrari's blog on banding?  It would appear this is exactly what you are looking for.    http://sqlblog.com/blogs/alberto_ferrari/archive/2010/10/12/banding-with-powerpivot.aspx

Hope that helps!

---

Javier Guillen
http://javierguillen.wordpress.com/

Answer 4

Javier,

Many thanks for your continuing assistance. I've already seen Alberto's very useful article and have tried to use it, but it doesn't seem to work for me.

What I'd like is to create a dynamic banding summary, for example

Band Name        Count         Total Value

0-£50K              100             £100,000.00

£50K - 100K       10              £500,000.00

£100K+              1                £100,000.00

The bands should come from an Excel linked table so that users can change the band names and min/max values. Additionally the count of customers in that band and the total value should also change based on user selections and filter - e.g. store, date and product.

Does this make sense?

Thankyou,

Matt

Answer 5

Javier,

I've been looking at this again. Am I mistaken in thinking that Albertos' solution would only return the band name as a measure, and not allow me to group the data as I require?

Matt

Answer 6

MAQ1,

Alberto's solution will return the band as a calculated column, not a measure. So you will be able to group the data by Sales bands.

Since the implementation uses calculated columns, the banding itself will not be sensitive to filter and slicer selections. Instead it will be dependent on the Sales total for each customer as calculated in the Customer table (which is for all products and all dates). However the Count and Total Value measures in your pivot table should be sensitive to your filter and slicer selections (if they were defined properly).

Answer 7

Hi MAQ1,

Yes, ruve1k is correct.   The implementation that Alberto proposes acts on calculated columns.  So here is the distinction: a slicer or filter selection can make a report dynamic and can have an impact on measures.   Calculated column values on the other hand, get evaluated once and then persisted.   So this may sound a bit confusing:  a linked table (which is what you can use for your banding situation), takes user input and then - upon refresh - forces calculated columns to re-evaluate using this input.   However, this is done only once - not on the fly as a measure would.

Linked tables can enable you to include user input and then with DAX you can categorize rows based on the arbitrary values used on the table.   Do you have some sample data you can post? In that way we can help you come up with a solution.

---

Javier Guillen
http://javierguillen.wordpress.com/

Answer 8

Hey Guys,

Thank you both for your continuing help. I understand now the distinction between calculated columns and DAX measures.

I think the problem is that my data is transactional. The fact table has 1 row for each combination of customer ID, date, product and store. I calculate the banding in the fact table using this formula:

= CALCULATE( VALUES('ValueBands'[Band Name] ),
FILTER( 'ValueBands',
Sales[Value Inc Tax] >= 'ValueBands'[MinValue]&& Sales[Value Inc Tax] < 'ValueBands'[MaxValue] ))

Therefore it's really each transaction that is assigned a banding and not the customer as a whole. If a customer has more than 1 transactions, they can fall into multiple bandings.

I guess one solution would be to only import the data that is needed for the analysis, but I'd really like to develop a solution that allows the user to filter the data as they require.

Matt

Answer 9

Hey Javier,

Have you had chance to review my comments?

I hope I haven't caused offence by unmarking your response as the answer?

Matt

Answer 10

Hi Matt!

No worries, I dont get offended for that! :-) 

Sorry it took a bit of time to respond.   I think I understand the issue now, it is a matter of granularity - or the proper level of granularity used to compute the output.  Generally, you can control the level of granularity used in a calculation by using iterative functions like SUMX in conjunction with SUMMARIZE.   I posted a small blog entry about a similar topic yesterday:  http://javierguillen.wordpress.com/2012/04/22/what-if-analysis-at-the-right-level-of-granularity-in-powerpivot/

Could you post a small sample of what your transaction data looks like? (3 or 4 rows)

---

Javier Guillen
http://javierguillen.wordpress.com/

Answer 11

Javier,

OK - I'll have to try harder next time to offend then ;->

I'll have a look at your blog post and see if I can apply it to my situation.

As you say, it seems to be a matter of granularity. I want to be able to import all data and then have the end-user select slicers to create the criteria they need. Here is a snapshot of the fact data:

Customer ID	Date	EAN	Quantity	GBP Value Exc Tax	Store Code	Band
18902	16/02/2008	963741	0	£0	151	1.  Returns and no sales
18902	23/08/2007	12345	0	£0	151	1.  Returns and no sales
18902	27/06/2007	12345	0	£0	151	1.  Returns and no sales
18902	16/05/2008	54321	0	£0	151	1.  Returns and no sales
18902	16/05/2008	111111	0	£0	151	1.  Returns and no sales
18902	30/08/2006	22222	-1	-£1,851.06	151	1.  Returns and no sales
852159	16/04/2012	33333	1	£28,369.17	151	2.  1 - 50K
18902	16/04/2012	55555	1	£52,312.5	151	3.  50K - 100K
18902	18/01/2012	44444	1	£19,858.33	151	2.  1 - 50K
845621	22/03/2011	66666	1	£131,433.33	151	4.  100K - 250K
18902	11/04/2008	54321	1	£36,271.49	151	2.  1 - 50K
18902	11/04/2008	111111	1	£22,978.72	151	2.  1 - 50K
357753	27/06/2007	77777	1	£18,949.79	151	2.  1 - 50K
18902	09/03/2007	12345	1	£11,489.36	151	2.  1 - 50K
101235	11/12/2006	88888	1	£11,513.19	151	2.  1 - 50K
106555	06/12/2006	99999	1	£10,955.74	151	2.  1 - 50K

The grain is customer, by day, by product (EAN), by store, giving me 4 dimensions. Each of these columns links to 4 dimension tables. Therefore I can then filter sales by Product Group, Store Division and Year.

What I want is for the banding to observe these filters. However at the moment, the banding is done at the lowest level of the grain.

Does this help?

Thank you again,

Matt

Answer 12

Hi Matt

Thanks for clarifying this a bit more.  You can compute the bands at a different level of granularity by using a measure (not a calculated column), with an expression like the following:

	CALCULATE( 
		VALUES( Bands[band name] ) ,  
		FILTER( 
			Bands, 
			SUM(Facts[GBP Value Exc Tax]) >= Bands[min value] && 
			SUM(Facts[GBP Value Exc Tax]) < Bands[max value]  
		)  
	)

With this, the bands won't be computed at the same level as the transaction/fact table; instead it will first aggregate and then compute the band.  For example, when computing the band for the aggregate tax by customer, you would get an output like:

The banding would work also if I replaced the Customer ID by Year (it will first aggregate the tax value for that year, and then compute the appropriate band)

---

Javier Guillen
http://javierguillen.wordpress.com/

Answer 13

Javier,

Thank you so much for your reply. I see how that works and have implemented it fine.

However, because it is a calculated column you cannot group on it. It will only return a result for a given intersection:

2009	8.  3M+	£6,440,343
2010	8.  3M+	£9,041,557
2011	8.  3M+	£9,994,742
2012	7.  1M - 3M	£2,624,025

Is it also possible to see a 'banding breakdown' by year (or other dimension for that matter). For example:

2010	7.  3M+	£6,440,343
	8.  1M - 3M	£9,041,557
2011	7.  3M+	£9,994,742
	8.  1M - 3M	£2,624,025

So therefore in 2010, banding 3M+ is the total for all customers who bought more than £3M+ accross the sales as determined by the slicers selected.

Kind regards - and thanks again,

Matt

Answer 14

Hi Matt,

Both things are possible.  For slicing on the banding values, you can create a slicer based on all the banding names (from the band table).  Then use an expression like the one below to great a new measure:

IF(NOT(ISBLANK(CALCULATE( 
		VALUES( Bands[band name] ) ,  
		FILTER( 
			Bands, 
			SUM(Facts[GBP Value Exc Tax]) >= Bands[min value] && 
			SUM(Facts[GBP Value Exc Tax]) < Bands[max value]  
		)  
	))) , [Sum of GBP Value Exc Tax])

With this, you can get a result like the following:

Regarding the second point, you can break down the banding by changing the granularity of your expression. Something like this should work:

IF(HASONEVALUE(Bands[band name]) , CALCULATE( 
		SUM ( Facts[GBP Value Exc Tax] )  ,  
		FILTER( 
			SUMMARIZE( Facts, Facts[Customer ID], "Amount", SUM( Facts[GBP Value Exc Tax] ) ), 
			[Amount] >= VALUES(Bands[min value]) && 
			[Amount] < VALUES(Bands[max value])  
		)  
	) )

Which will give you and output by year but broken down by band names (in other words, it aggregates all customers for which their amounts fall within that bucket): The output is the following:

---

Javier Guillen
http://javierguillen.wordpress.com/

Answer 15

Javier,

Many thanks (again) for your help.

I think the second example is what I need. Am I right in thinking the calculation would be a calculated column in the Fact (sales) table? I've added it as one, but nothing is returned by the formula:

=IF(HASONEVALUE(ValueBands[Band Name]) , CALCULATE( 
		SUM ( Sales[GBP Value Exc Tax] )  ,  
		FILTER( 
			SUMMARIZE( Sales, Sales[Customer ID], "GBP Value Exc Tax", SUM( Sales[GBP Value Exc Tax] ) ), 
			[GBP Value Exc Tax] >= VALUES(ValueBands[MinValue]) && 
			[GBP Value Exc Tax] < VALUES(ValueBands[MaxValue])  
		)  
	) )

I have replaced "Amount"with "GBP Value Exc Tax"

I've also upgraded to PowerPivot 2012 to take advantage of the 'HASONEVALUE' function

Matt

Answer 16

Hi Matt,

Yes, both HASONEVALUE and SUMMARIZE are functions only available in PowerPivot 2012.

The formula is actually intended as a measure, not as a calculated column.

If you want, send me your email and I will send you an example file of the formula working.

---

Javier Guillen
http://javierguillen.wordpress.com/

Answer 17

Javier,

I've dropped you an email!

Matt

Answer 18

hi please check this link, it may help you.

http://www.powerpivotpro.com/2010/04/six-months-with-powerpivot-part-one/