PowerPivot relationship trouble

[bvacha]

I'm having trouble getting started with relationships in PowerPivot. I've created a dimension table and a fact table and put those in the data model. I linked the many-to-one relationship, but when I create the Pivot table and drag in fields from the dimension and the fact table, the summed dollar values from the dimension table are always displayed as totals. I expect them to be filtered by the dimension attributes. Perhaps someone can point me to a simple example or perhaps someone is aware of a bug or common issue that I'm missing. If I could post attachments here I would love to share and get your thoughts on where I'm going wrong.

 

[GDRIII]

Try dragging one of your dimension attribute columns and drop the field in the Row Labels or Column Labels section of the Field List.

If you have Sum of Sales as a measure and drop Customer Type onto Rows you should get a breakdown by Customer Type.

Take it a step further and drop Month from a linked Calendar table on columns and you're starting to cook.

Take it a step further and drop state below customer type on rows and you got some pretty powerful data at your finger tips.

Throw Salesmen in a slicer and now you are toggling through some amazing data.

 

[bvacha]

GDRIII, what excel file are you referencing with Customer Type, Month, Calendar table, etc? When I try to do a similar thing with my file, the breakdown works well within a single dimension table. But as soon as I bring in a fact, the breakdown fails. This is with a sum of value field. To me, it's acting just like I don't have a relationship, but I do. I'd like to see a working sample file.

 

[GDRIII]

I was just using some fictitious standard every day data for an example.

Measures come from the fact table. Rows, columns and slicers should be from your dimensional tables. Facts don't work so well in those slots...

Maybe I'm not following you well.

 

[scottsen]

Power Pivot Relationships | Power Pivot | Tiny Lizard

 

[bvacha]

Thanks so much GDRIII and scottsen!! You're spot on, GDRIII, in the fact that I didn't understand that I should put the measures in the fact table. I understand that the related() function will get me what I need. scottsen, I'll check out the link for more learning. You guys are wonderful!

 

[GDRIII]

Glad to help.

Get yourself a copy of DAX Formulas for PowerPivot by Rob Collie...best cash you'll ever spend