Hello, I have the following table, how do I get the percentage from the following table and add a new column in table 1?Table 1 OfficeOccupancyIdOccupancyDateOfficeAreaIdSlotsParkingSlotPercentageAREA1_2021102525-Oct-21AREA115%AREA1_202109088-Sep-21AREA12%AREA10_2021101919-Oct-21AREA100%AREA10_2021102020-Oct-21AREA100%AREA10_2021102121-Oct-21AREA100%AREA11_2021102626-Oct-21AREA110%AREA11_2021102727-Oct-21AREA110%AREA12_202109088-Sep-21AREA122%AREA12_202109099-Sep-21AREA122%AREA14_2021093030-Sep-21AREA1416%AREA14_202110011-Oct-21AREA142%AREA15_202110011-Oct-21AREA152%AREA15_202110022-Oct-21AREA152%AREA16_2021102525-Oct-21AREA160%AREA16_2021102626-Oct-21AREA160%AREA17_2021101313-Oct-21AREA1710%AREA17_2021101414-Oct-21AREA1710%AREA18_2021111313-Nov-21AREA1820%AREA18_2021111414-Nov-21AREA1820%AREA19_2021101313-Oct-21AREA1910%AREA19_2021101414-Oct-21AREA199%AREA6_202110011-Oct-21AREA62%AREA6_202110022-Oct-21AREA62%table 2 OfficeAreaIdLabelLabelFrOfficeAreaIsDeletedOfficeIdTotalAvailableSlotAREA8Support areaZone SupportFalseO93AREA6Marketing areaZone MarketingFalseO110AREA15Crowded areaZone encombréeFalseO12AREA14HR areaZone testFalseO92AREA12Finance areaZone FinanceFalseO110AREA1Sales areaZone CommerceFalseO114Thanks

Hi Anonymous , You can use USERRELATIONSHIP function to return the values from table 2. TotalAvailableSlot from Table2 = CALCULATE(SUM('Table2'[TotalAvailableSlot]),USERELATIONSHIP(Table1[OfficeAreaId],Table2[OfficeAreaId])) Then you can add DIVIDE function to calculate the percentage. ParkingSlotPercentage = DIVIDE(CALCULATE(SUM('Table2'[TotalAvailableSlot]),USERELATIONSHIP(Table1[OfficeAreaId],Table2[OfficeAreaId])),[Slots]) As for the percentage in Table 3 in your new response, how is it calculated? Best Regards, Stephen Tao If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous

Not applicable

4 years ago

Solved

Get percentage from two table

Hello,

I have the following table, how do I get the percentage from the following table and add a new column in table 1?

Table 1

OfficeOccupancyId	OccupancyDate	OfficeAreaId	Slots	ParkingSlotPercentage
AREA1_20211025	25-Oct-21	AREA1	15	%
AREA1_20210908	8-Sep-21	AREA1	2	%
AREA10_20211019	19-Oct-21	AREA10	0	%
AREA10_20211020	20-Oct-21	AREA10	0	%
AREA10_20211021	21-Oct-21	AREA10	0	%
AREA11_20211026	26-Oct-21	AREA11	0	%
AREA11_20211027	27-Oct-21	AREA11	0	%
AREA12_20210908	8-Sep-21	AREA12	2	%
AREA12_20210909	9-Sep-21	AREA12	2	%
AREA14_20210930	30-Sep-21	AREA14	16	%
AREA14_20211001	1-Oct-21	AREA14	2	%
AREA15_20211001	1-Oct-21	AREA15	2	%
AREA15_20211002	2-Oct-21	AREA15	2	%
AREA16_20211025	25-Oct-21	AREA16	0	%
AREA16_20211026	26-Oct-21	AREA16	0	%
AREA17_20211013	13-Oct-21	AREA17	10	%
AREA17_20211014	14-Oct-21	AREA17	10	%
AREA18_20211113	13-Nov-21	AREA18	20	%
AREA18_20211114	14-Nov-21	AREA18	20	%
AREA19_20211013	13-Oct-21	AREA19	10	%
AREA19_20211014	14-Oct-21	AREA19	9	%
AREA6_20211001	1-Oct-21	AREA6	2	%
AREA6_20211002	2-Oct-21	AREA6	2	%

table 2

OfficeAreaId	Label	LabelFr	OfficeAreaIsDeleted	OfficeId	TotalAvailableSlot
AREA8	Support area	Zone Support	False	O9	3
AREA6	Marketing area	Zone Marketing	False	O1	10
AREA15	Crowded area	Zone encombrée	False	O1	2
AREA14	HR area	Zone test	False	O9	2
AREA12	Finance area	Zone Finance	False	O1	10
AREA1	Sales area	Zone Commerce	False	O1	14

Thanks

Need Help

Anonymous
4 years ago
Hi Anonymous ,

You can use USERRELATIONSHIP function to return the values from table 2.

TotalAvailableSlot from Table2 = CALCULATE(SUM('Table2'[TotalAvailableSlot]),USERELATIONSHIP(Table1[OfficeAreaId],Table2[OfficeAreaId]))

Then you can add DIVIDE function to calculate the percentage.

ParkingSlotPercentage = DIVIDE(CALCULATE(SUM('Table2'[TotalAvailableSlot]),USERELATIONSHIP(Table1[OfficeAreaId],Table2[OfficeAreaId])),[Slots])

As for the percentage in Table 3 in your new response, how is it calculated?

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Get percentage from two table.pbix50 KB

8 Replies

PaulDBrown
Community Champion
4 years ago
How is the model set up?
- Anonymous
  Not applicable
  4 years ago
  Hi Paul,
  
  Thanks for reply my post.
  
  here's the relation table
  
  is it possible to get the percetage ?
  The idea is like the following :
  OfficeOccupancyId OccupancyDate OfficeAreaId Slots ParkingSlotPercentage
  AREA1_20211025 25-Oct-21 AREA1 15 94%
  OfficeAreaId Label LabelFr OfficeAreaIsDeleted OfficeId TotalAvailableSlot
  AREA1 Sales area Zone Commerce FALSE O1 14
  (14/15) * 100 =94%
  - Kumail
    Impactful Individual
    4 years ago
    Hello PaulDBrown
    
    If you could send sample .pbix that demonstrate what you are looking to get. It would really help providing you a quick solution.
    
    You can send the sample .pbix file by adding it to your drive or dropbox and add the link here.
    
    Regards
    Kumail Raza

PaulDBrown

Community Champion

4 years ago

For a calculated coloumn in Table 1:

% Occupancy =
VAR SlotsAvailable =
    CALCULATE (
        SUM ( 'Table 2'[TotalAvailableSlot] ),
        FILTER ( 'Table 2', 'Table 1'[OfficeAreaId] = 'Table 2'[OfficeAreaId] )
    )
RETURN
    DIVIDE ( SUM ( 'Table 1'[Slots] ), SlotsAvailable )

As a measure:

% Occupancy = 
DIVIDE(SUM('Table 1'[Slots]), SUM('Table 2'[TotalAvailableSlot]))

I've attached the sample PBIX file

Slots.pbix35 KB

Anonymous

Not applicable

4 years ago

Hi Paul,

Thanks for your reply. It's good.

But there is other table which i'm not aware.

Table 1

OfficeOccupancyId	OccupancyDate	OfficeAreaId	Slots
AREA1_20211025	25-Oct-21	AREA1	15
AREA1_20210908	8-Sep-21	AREA1	2
AREA10_20211019	19-Oct-21	AREA10	0
AREA10_20211020	20-Oct-21	AREA10	0
AREA10_20211021	21-Oct-21	AREA10	0
AREA11_20211026	26-Oct-21	AREA11	0
AREA11_20211027	27-Oct-21	AREA11	0
AREA12_20210908	8-Sep-21	AREA12	2
AREA12_20210909	9-Sep-21	AREA12	2
AREA14_20210930	30-Sep-21	AREA14	16
AREA14_20211001	1-Oct-21	AREA14	2
AREA15_20211001	1-Oct-21	AREA15	2
AREA15_20211002	2-Oct-21	AREA15	2
AREA16_20211025	25-Oct-21	AREA16	0
AREA16_20211026	26-Oct-21	AREA16	0
AREA17_20211013	13-Oct-21	AREA17	10
AREA17_20211014	14-Oct-21	AREA17	10
AREA18_20211113	13-Nov-21	AREA18	20
AREA18_20211114	14-Nov-21	AREA18	20
AREA19_20211013	13-Oct-21	AREA19	10
AREA19_20211014	14-Oct-21	AREA19	9
AREA6_20211001	1-Oct-21	AREA6	2
AREA6_20211002	2-Oct-21	AREA6	2

table 2

OfficeAreaId	Label	LabelFr	OfficeAreaIsDeleted	OfficeId	TotalAvailableSlot
AREA8	Support area	Zone Support	False	O9	3
AREA6	Marketing area	Zone Marketing	False	O1	10
AREA15	Crowded area	Zone encombrée	False	O1	2
AREA14	HR area	Zone test	False	O9	2
AREA12	Finance area	Zone Finance	False	O1	10
AREA1	Sales area	Zone Commerce	False	O1	14

Table 3

OfficeOccupancyId	User ID	%
AREA8_20211118	A	?
AREA8_20211104	B	?
AREA8_20210926	A	?
AREA6_20211110	A	?
AREA6_20211103	A	?
AREA6_20211102	A	?
AREA6_20211101	C	?
AREA6_20211028	D	?
AREA6_20211028	C	?
AREA6_20211022	E	?
AREA6_20211021	B	?
AREA6_20211020	A	?
AREA6_20211019	C	?
AREA6_20211015	C	?
AREA6_20211014	B	?
AREA6_20211012	D	?

PaulDBrown
Community Champion
4 years ago
Sorry, I don´t know what you need from table 3. Can you please clarify?

Forum Discussion

Get percentage from two table

8 Replies

Related content

Percentage by group

Calculate percentage from two Tables

Converting to Percentage

Percentage Formatting

Categorise average percentages