How do model data with different granularity?

[tvaishnav]

I have a situation where I want to compare cost and revenue. My revenue number is just an aggregate, however cost has sub categories. How would you model this?

I can think of these ways.

1) Put revenue in one row and have 0s in all others 

Project	Revenue	Cost	Cost Category
Project A	1000000	500	Cat 1
Project A	0	90000	Cat 2
Project A	0	8855	Cat 3
Project A	0	6000	Cat 4

2) Make single column for cost and revenue.

Project	Type	Value
Project A	Revenue	100000
Project A	Cost Cat 1	500
Project A	Cost Cat 2	90000
Project A	Cost Cat 3	8855
Project A	Cost Cat 4	6000

3) Create two fact tables one for revenue and another for cost.

In general, I have been struggling with this concept of different grains in data for a while. Any pointers on what are the best practices? 

[PaulDBrown]

I would go for option "3) Create two fact tables one for revenue and another for cost" and have a bridge table for Project and any other fileds common to both tables.

[tvaishnav]

Thank you for your response. Do you think I can create this using matrix visual and two fact tables?

[PaulDBrown]

Sure, but you will need a table with the row structure for the matrix. SImilar to this, but for rows instead of columns:

Custom Matrix 

[johnt75]

Have a read of https://www.daxpatterns.com/budget/ by the SQLBI guys, it should give you some ideas about working at different granularities.