Forum Discussion
Measures through relations
Dear,
Here my still little project and two questions.
Project.
Habs ( dimension table )
Books (dimension table )
KPI ( fact table)
Hotel ( fact table )
Calendar ( dimension table )
Model
Question 1. I'd need a measue that after filtering by "hotel" and "date" it shows the total of avialable rooms for a hotel, independent of the date.
Hotel Madrid have 4 Estandard rooms and 2 Superior Room.
Hotel Madrid on 1st January 2021 has ocupaid 2 Estandar and 2 superior.
Final report would be:
Question 2. From table KPI how can I calculate through a measure: total number of days with sales for each hotel ( this measure should remain the same if filtered by room type )
Thank you in advance,
Adrian
Hi ADRICHECKIN ,
Believe that you have presented one format but asked for another measure so taking your text into account for calculating the availability by day you need to do the following. Create 2 Measures:
Total Rooms in hotel = COUNTROWS(FILTER(HABS, HABS[HOTEL] in VALUES(Hotel[Hotel]))) Available rooms = [Total Rooms in hotel] - COUNT('KPI'[UDS])Then use the ones you need on your visualization, be aware that this only work for a single day selection, if you need this in a period the calculations needs to be changed.
Regarding the second calculation this also depends on how you want to present if you are working with a card visualization for example and turn off the interactions on for the slicer a simple measure like this is enough:
Total days with sales = DISTINCTCOUNT('KPI'[Date])If you want to add this to your table visualization then you need to make it like this:
Total days with sales = CALCULATE(DISTINCTCOUNT('KPI'[Date]), ALL('CAlendar'[Date]))Check PBIX file attach.
1 Reply
Hi ADRICHECKIN ,
Believe that you have presented one format but asked for another measure so taking your text into account for calculating the availability by day you need to do the following. Create 2 Measures:
Total Rooms in hotel = COUNTROWS(FILTER(HABS, HABS[HOTEL] in VALUES(Hotel[Hotel]))) Available rooms = [Total Rooms in hotel] - COUNT('KPI'[UDS])Then use the ones you need on your visualization, be aware that this only work for a single day selection, if you need this in a period the calculations needs to be changed.
Regarding the second calculation this also depends on how you want to present if you are working with a card visualization for example and turn off the interactions on for the slicer a simple measure like this is enough:
Total days with sales = DISTINCTCOUNT('KPI'[Date])If you want to add this to your table visualization then you need to make it like this:
Total days with sales = CALCULATE(DISTINCTCOUNT('KPI'[Date]), ALL('CAlendar'[Date]))Check PBIX file attach.