Getting a sum of a percentage in my visual instead of an average for the week

[Crowdak]

Hello,

I am trying to get a visual that shows 3 things: Average of % reservation of an office per week, Average of % presence of an office per week, Max % presence of an office within the week.

Right now I've been doing the percentage, for both reservation and presence, using a measure. But when I add it to a visual, I get the sum of every % instead of the average of each day in the week.

Maybe the method I use isn't the proper one 

Table
Reservation
Name	Email	Office	Date
John Smith	jsmith@contoso.com	A1	10-Oct-24
John Smith	jsmith@contoso.com	A1	11-Oct-24
Bob Loblaw	bloblaw@contoso.com	A3	10-Oct-24

Table
Presence
Name	Email	Office	Date
John Smith	jsmith@contoso.com	A1	10-Oct-24
John Smith	jsmith@contoso.com	A1	11-Oct-24
Bob Loblaw	bloblaw@contoso.com	A3	10-Oct-24
Bob Loblaw	bloblaw@contoso.com	A2	11-Oct-24

Table
Reservable Offices
Office	Reservable
A1	TRUE
A2	TRUE
A3	TRUE
A4	FALSE

Measures

Total amount of reservable office =  CALCULATE(COUNT('Total amount of reservable office'[Office]), ('Total amount of reservable office'[Reservable]="True"))

%Reservation = CALCULATE((COUNT('Reservation'[Name]))/([Total amount of reservable office]))

Can you help out a newby? 

Also, when I right click on the measure in my Y Axis, I don't see the options to change the agreggation to AVG instead of sum.

[Anonymous · Accepted solution]

Thanks for the reply from FreemanZ , please allow me to provide another insight:

Hi, @Crowdak 

1.Firstly, you might also consider trying the following measure. However, please note that the time column in the VALUES() function may need to be adjusted based on your actual data, as I currently have limited information and am unsure about your table relationships.

Avg % Reservation = 
AVERAGEX(
    VALUES('Reservation'[Date]),
    CALCULATE(COUNT('Reservation'[Name]) / [Total amount of reservable office])
)

Avg % Presence = 
AVERAGEX(
    VALUES('Presence'[Date]),
    CALCULATE(COUNT('Presence'[Name]) / [Total amount of reservable office])
)

Max % Presence = 
MAXX(
    VALUES('Presence'[Date]),
    CALCULATE(COUNT('Presence'[Name]) / [Total amount of reservable office])
)

2.Secondly, if you have a clear understanding of your calculation logic, the most effective approach would be to convert your measure into a calculated column. This would allow for quicker aggregation, for example:

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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

[Crowdak]

Thanks a lot for your help

[FreemanZ]

hi @Crowdak ,

we can select avg, min, max of a native column field, but we cant do that for measures.

so in your case, you would need to write separate measures for average, max values.