cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Sum amount of fact table based on higher grain.

Hello, 

Here I have a table like this.

 

In this Fact_Booking table, the grain of this table is booking detail. One line in this table equals to a line in a booking.

For example, booking #1 has 3 booking detail lines (#1, #2, #3), booking #2 has 2 detail lines, booking 3, 4, 5 have only 1 line per each.

 

BookingDetailAmount is the price for that line, while BookingAmount is the price for the whole booking.
For example, the whole price of booking #1 is 40. Booking 1 have 3 lines with 3 line prices, 20+10+10 = 40 (This is true in most cases, but not always.) .

Question 1:  I want to calculate the number of INVOICE (NOT invoice line) of a Company.

And I want to calculate the total booking amount using BookingAmount attribute, because the booking amount of a booking is not guaranteed to be the sum of all its BookingDetail line prices. No one in my company confirms that, and they've always been using the BookingAmount to calculate, so I must also follow that custom.)

 

The DIM_Company table

The result I want: 

 

I have tried many ways but it always returns wrong result like this. 
Question 2:

Is this fact table well designed (especially for BI tools like powerBI) ? As far as I know, fact should use the best grain the system can have. So in our data warehouse, the data of Booking is merged and joined to the highest grain (here is booking detail), resulting in a fact table like this. But in realilty, the booking detail lines don't have much meanings as some of the most important calculation is calculated base on the number of the higher grain (Booking). 

 

So should i separate Booking and BookingDetail into two fact table, or keep this kind of fact-table designing approach? 

 

1 ACCEPTED SOLUTION
v-xicai
Community Support
Community Support

Hi @Anonymous ,

 

For your first demand, you can create measures like DAX below.

 

Number of bookings = DISTINCTCOUNT(Fact_Booking[BookingKey])
 
Total Amount of Every Booking =
var _table=SUMMARIZE(Fact_Booking,Fact_Booking[CompanyKey],Fact_Booking[BookingKey],"b",DISTINCT(Fact_Booking[BookingAmount]))
return
CALCULATE(SUMX(_table,[b]),ALLEXCEPT(Fact_Booking,Fact_Booking[CompanyKey],Fact_Booking[BookingKey]))

Result:

38.png

 

 

 

 

For your second demand : >>Also, I want to know if the design of this fact table is good for BI tool like power BI to calculate things?

 

 

The created many to many relationship between the two fact tables is no problem for further calculation, you may refer to the link to learn more : https://docs.microsoft.com/en-us/power-bi/desktop-many-to-many-relationships.

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User III
Super User III

Hi,

Your question is not clear.  Explain the question with a smaller and legible dataset and show the expected result.  Also, share the link from where i can download the PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

I have massively simplified my data, let me have some clarifications first.

 

There is a Fact_Booking table that contains booking data in our system. This table is from data warehouse, and it has been joined and denormalized to booking detail grain.

 


BookingKey is the real Booking ID in the OLTP system.

BookingDetailKey is the real BookingDetail ID in the OLTP system.

Status indicates that if the booking has been finished or still being processed.

Company is the customer of our system.

 

BookingQuantity is just a calculated column. Values always equals to 1. I just use make itto make PowerBI automatically calculate the number of line for me.


Booking amount is the amount of money for that whole booking. For example, BookingKey 1 has a booking amount of 40. This means our Customer (Company 1) must pay this amount of money for this booking.

 

BookingDetailAmount is the amount for a line in a booking. A booking may have only one or >2 lines. The BookingAmount is usually the sum of all BookingDetailAmount, but there is no guarantee. Perhaps some additional fees may be added in for some cases. And this is where the problem occured.

 

I want to calculate the total booking amount for each company. Calculation must use the BookingAmount to calculate (because the above reason), not the BookingDetailAmount.

 

When I drag the Company Name in, then drag the booking amount in, Power BI returns wrong result. I guess that it is because it sums all the booking amount of every line for each company, and some booking has more than 2 line (2 booking details), resulting wrong result.


The problem also occurs to the count of booking. I want to count the amount of booking for each company, but it seems like the Power BI is returning the count of booking detail for each company.  The image below is the wrong result that PowerBI returns to me.


So how can I calculate the total booking amount for each company based on BookingAmount. I want the result to be like this.

 

 

Here is the link of the PowerBI file

https://drive.google.com/open?id=1VNLMHiP0xSsNAyXsRDrdhNZAvleMh8Qs

 

Also, I want to know if the design of this fact table is good for BI tool like power BI to calculate things?

Also, please see the power BI file. In that file, I also included another fact table, named "Fact_Invoice".
I want to ask if it is good that 2 fact tables are linked together like in the file. The 2 fact share the shame degenerate dimension (BookingID), so I connect the 2 fact table like that. I want to know if this is legit or not.

 

v-xicai
Community Support
Community Support

Hi @Anonymous ,

 

For your first demand, you can create measures like DAX below.

 

Number of bookings = DISTINCTCOUNT(Fact_Booking[BookingKey])
 
Total Amount of Every Booking =
var _table=SUMMARIZE(Fact_Booking,Fact_Booking[CompanyKey],Fact_Booking[BookingKey],"b",DISTINCT(Fact_Booking[BookingAmount]))
return
CALCULATE(SUMX(_table,[b]),ALLEXCEPT(Fact_Booking,Fact_Booking[CompanyKey],Fact_Booking[BookingKey]))

Result:

38.png

 

 

 

 

For your second demand : >>Also, I want to know if the design of this fact table is good for BI tool like power BI to calculate things?

 

 

The created many to many relationship between the two fact tables is no problem for further calculation, you may refer to the link to learn more : https://docs.microsoft.com/en-us/power-bi/desktop-many-to-many-relationships.

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Anonymous
Not applicable

Thank for your help.

Actually, relating the design of the fact table, I want to ask if the Booking fact table with a grain of Booking detail like this one is legit, good design and suitable for BI Tool (eg: aggregating performance) or not. Especially that some calculation must be calculated basing on the number of higher grain. I don't know if I should keep this design or I should re-design the fact table.

I'm in progress of developing a Data Warehouse for our company, but because I'm just new to this, so I try to follow the best pratices as much as possible, in order to avoid troubles that may occur in the future. Many sources claim that the fact table should be the highest possible. FactInternetSales table of AdventureWorkDW example database is also like this, one OrderNumber may have 1 or >2 rows, with different OrderLineNumbers... 

But I don't really know if this approach is approriate, espcially in case with  some exception like the problem I'm asking in this topic. That's why I have such this question about the design of the fact table.
 

Also, I have read sources claiming that Fact tables should not be linked together, they must be linked by Dimension. But here the two fact table shares the same BookingID, which is a degenerate dimension. So I want to ask if it's legit to join 2 two fact table using this BookingID degenerate dimension like this, and want to know if PowerBI can handle this efficiently. (Because in the relation visual, it really seems like I'm joining two fact table together, on top of that, the relation between 2 tables is many-to-many which really bugs me)

Anonymous
Not applicable

Bump.

Can somebody help?

Anonymous
Not applicable

Bump.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors