Design Best Practice

[SimonNU]

To simplify my problem: I have one table which will contain 50 metrics that apply to these data sources: Current Year, Prior Year, Forecast and Budget.

Table example:
dataSource | Period | Account | Amount
Current Year | 1 | Gross Services | $10
Prior Year | 1 | Gross Services | $20
Forecast | 1 | Gross Services | $30
Budget | 1 | Gross Services | $40

I believe I have two options for designing these 50 metrics:

1. Design 50 base measures and let the user apply the dataSource filter to "correct" them;or
2. Create 50 measures for for each data source (200 in total) and bloat the pivot table item selection list.

Which is best practice? What are the pros/cons?

Furthermore, the user would need to see variance columns in a pivot table, e.g. (Actual Gross Services (GS) - Budget GS)/Budget GS. Would I need to design a variance measure for each measure?


Thanks!

 

[scottsen]

You are almost certainly going to end up with Total CY, Total PY, Total Forecast, Total Budget measures (as opposed to having a user click a slicer for that field, which is crazy talk) -- if nothing else because you are going to want [YoY Change], and [% of Budget], etc... measures that span multiple data source types.