Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
Guataha
Frequent Visitor

Calculations within a single fact column

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.

5 REPLIES 5
Anonymous
Not applicable

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.

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
Not applicable

@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
Not applicable

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

Unfortunately this is not an option

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.