Problems with modeling data

[cole_lehmkuhler]

I have a file of all sales invoices. I am trying to compare Q1 of 2015 to Q1 of 2016 by a company's branch and have it drill down to salesman and drill down to by our sales divisions. I want this to be in a clustered bar graph which shouldn't be too complicate. I have the drill down part figured out already, but I'm having a hard time with getting the 2 quarters sales by branch, salesman, and divisions in the same column so I can add both of them to the values field in the visualization. I can post some screenshots if this is confusing just let me know if you would like them. Thanks!

[jpereztang]

Also think about if the chart you select is the best choice for the information you need to show

This imagen can guide you to select the best graph considering some stuff

http://1.bp.blogspot.com/_ya8226kAZGQ/SxSVaPcuAAI/AAAAAAAAAt8/S4TmxbTISI4/s1600/graficadegraficas.jp...

[Seth_C_Bauer]

@cole_lehmkuhler What does the current model look like? Is this all in one table, or do you have lookup tables (dimensions) as well? In order to do time intelligence calculations you will need a date table, have you one of those in your model?

[cole_lehmkuhler]

@Eno1978 It's all in one table. It has date. the sale amount the division that this product was in the salesman who sold it and which branch sold the product. It has all of the pieces that it needs I just don't know how to model it so that I can get these two quarters sales along with the breakdown of branch,salesman,division all in the same table. I have tried referencing the table twice and filtering so that only the sales from these two quarters are displayed in their own tables, but I don't know how to then either get them in the same table so I can use these values or possibly get a relationship betweent the two so I could use the sums of the sales amount to get them in the same clustered bar graph. Thanks for your reply!

[kcantor]

@cole_lehmkuhler I would recommend using the query editor to break out some of your information into lookup tables. Then use the lookup tables to populate the tables and pull the values from the combined fact table. You would have a lookup table for date, salesperson, branch, and division. Tie your tables together using relationships and it should allow you to get the necessary view.

In query editor, you can duplicate the fact table and remove columns and duplicates to create look up tables or you can create them in excel and pull them in. I have found the data and visuals respond better when using lookup tables as all filters flow downhill.

Here is some information on lookup tables:

http://newtech.about.com/od/enterprisedatawarehousing/a/Powerpivot-For-Excel-Dimension-Lookup-Tables...

[cole_lehmkuhler]

@Seth_C_Bauer

[Seth_C_Bauer]

@cole_lehmkuhler I agree with @kcantor I just couldn't find a straightforward link to describe things faster 🙂 
From there, to do your Quarter comparison, you could either create two seperate measures and throw those in the visual, or if you wanted to do an actual Quarter over Quarter you will find that a bit more advanced, and details here.