topic Re: Getting a sum of a percentage in my visual instead of an average for the week in DAX Commands and Tips

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

Crowdak — Thu, 31 Oct 2024 17:59:25 GMT

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.

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

FreemanZ — Thu, 31 Oct 2024 22:05:01 GMT

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.

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

Anonymous — Mon, 04 Nov 2024 07:09:12 GMT

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.

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

Crowdak — Mon, 04 Nov 2024 20:02:11 GMT

Thanks a lot for your help