How to create report by line in which each line is independent formular from many table

[Anonymous]

I work on the hospitality industry.

I have a very simply report on excel and I want to use power BI to create this report but it seem very difficult to do that in Power BI.

My template report is as following:

1. Number of room avaible to sold = Total existing room in table Room statistic by date, month, year by Hotel

2. Number of room sold = Total room sold in table Room statistic by date, month, year by Hotel

3. Breakfast F&B cover = Total guest use buffet => Come from SKF talbe

4. Lunch F&B Cover = Total guest use lunch buffet  => Come from SKF table

5. Average F&B per cover = Total F&B revenue (in Finance table)/Total F&B Cover (in SKF table)

So how can  I create above report that include many formular and its data come from many table in Power BI.

Please help me in this case because the problem is not in data, it is due to funtion in power bi.

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Thanks to rajulshah's suggestions, please check the links from his suggestions, it would help you a lot.

Besides, to answer your questions with detailed solution, please tell me what your table structure and relationships among tables.

Here is an exmaple:

to answer your 1,2 questions, i assump you have the table Room statistic as below:

You could create a date table, create relationships between tables.

calendar = CALENDARAUTO()

Create measures in table Room statistic, add columns and measures in a matrix table as below:

Number of room avaible to sold = CALCULATE(COUNT('Room statistic'[room id]),FILTER('Room statistic','Room statistic'[status]="on sale"))

Number of room sold = CALCULATE(COUNT('Room statistic'[room id]),FILTER('Room statistic','Room statistic'[status]="was sold"))

For other questions, could you share more information?

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rajulshah]

Hello @Anonymous ,

So basically I believe you want to replicate from Excel to Power BI. 

You can fetch your data in Power BI Desktop. Please refer link below:
https://docs.microsoft.com/en-us/power-bi/service-desktop-files

Set relationships in Power BI Desktop. Please refer link below:
https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships

And then create measures and plot them in the visulaizations:
https://docs.microsoft.com/en-us/power-bi/desktop-tutorial-create-measures
https://docs.microsoft.com/en-us/power-bi/desktop-measures

Also, there are enormous links which gives you guided instructions to do the same.

Hope I understood your issue correctly.