Calculations within a single fact column

[Guataha]

In my organisation, they have created a data set, with th following simple lay-out:

Time // Location // Measure // Value

In this way, they have stored, price, volume, availability, etc. data in one single fact column. Around this fact table there all kind of additional tables with dimensions, but these are not relevant for my question. The facts are available for each hour.

The issue is that I want to calculate the net sales (price * volume) for each hour and than sum the result for each hour up. After that I want to be able to slice and dice using for instance location and year/month.

If Price and Volume, would have been in two colums, this would be very easy. But with this single colom, I am little bit stuk on how to do solve this. So first question, is this even possible and any hint on what formula to use will be welcome.

[Anonymous]

Yes, it is possible to give you a formula but 1) it'll be extremely ugly, 2) it'll be extremely sub-optimal (meaning: slow) and 3) it'll be unnecessarily complex (which, again, means slow). What's more, the "fact table" is totally unsuitable for modeling in Power BI. It even goes against Best Practices of dimensional modeling. Are the ones who created the table even aware of their cardinal sins? I can't but wonder why people want to always sqeeze a square peg into a round hole and then they'll try hard to figure out why the model/system is slow and unusable. Do they really need to always learn the hard way? Really?

I'll tell you exactly why the solution with such a bad fact table will be totally flawed and unmaintainable. This is because you'll have to pair rows from the table for different measures (e.g., Price and Volume) and this will have to be done by joining on ALL the other columns. If you ever add more columns (attributes) to the model, you'll need to update the measures (possibly all of them!) and they will become slower and slower, precisely because the engine will have to work extremely hard to match rows from the same table and then do iteration over the matches executing operations like multiplication, division, addition.... over them. This will quickly become a nightmare for the developer to maintain and the user to use. I TELL YOU THAT TODAY. If you go down this route, you and the business will be sorry rather sooner than later. If I were you, I'd think twice before shooting myself in the foot.

[Guataha]

Thanks for the valuable feedback. The thing is that I did not designed the table, do not own the data and has anything to say about the database.

I only want to create a report based on this data. I will use your input for having an internal discussion on the data set-up, instead of trying to make this measure. Again thanks.

[Anonymous]

@Guataha 

Yeah. It's better to discuss the problem and find a good solution instead of having to constantly explain why things are not as smooth as they should be. PBI is great but only on condition that you get your data into the right shape. If you don't, prepare to suffer.

[Anonymous]

Just use Power Query to pivot the table into the right format.

[Guataha]

Unfortunately this is not an option