How to handle repeating values in fact table due to few numeric columns being at a higher grain?

Question

I am looking at a fact table which has a certain grain. So each record represents a transaction at that grain. Let's say the grain is header detail sub-detail.

But there are some numeric columns in this table that have values of a higher grain. That is header and detail. So it doesn't make sense to perform sum on these values.

I'm thinking of ways to make this model better so that when user creates a visual from this fact table, then the higher grain columns can still be utilized, and the user will not be allowed to SUM on those column.

One option is to create a measure that does an average of those column.

Another option is to create a measure that returns the first value from the column.

Which of the above approaches is recommended and is there any other approach?

score 2 · Accepted Answer · answered Apr 22 '23 at 17:48

2

is there any other approach?

The obvious approach is to move those columns to fact table at a different grain. Power BI also supports automatically switching queries from detail tables to aggregate tables where possible. See User-defined aggregations.

answered Apr 22 '23 at 17:48

David Browne - Microsoft

80,331
6
39
67

If I move these tables to different grain then I loose the drill through ability on the fact table – variable Apr 22 '23 at 22:08
Not if you have both fact tables in the model. – David Browne - Microsoft Apr 22 '23 at 23:00
May I request for an example to understand this. If I have a table visual at a higher grain (that is looking at FactTable1), then how can I have drill-through into table visual that is looking at FactTable2? – variable Apr 23 '23 at 11:06
Sure. Add some sample data to your question and I’ll explain. – David Browne - Microsoft Apr 23 '23 at 15:20
For example say header, detail, subdetail. – variable Apr 25 '23 at 19:47

score 0 · Answer 2 · answered Jul 11 '23 at 20:09

One alternative approach (which might not fit your situation) is to allocate the number from the header down to the detail level. So if there are 5 detail rows, divide by 5 (or some other weighting). This can then be summed.

This approach would depend on there being some meaning to a detail line taking its fair share of the header number though.

How to handle repeating values in fact table due to few numeric columns being at a higher grain?

2 Answers2