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

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

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?

Solution

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.