Last 30 Days and Last 7 Days measure.

[alexwardeee]

Hi all,

I just wanted to ask what is the best way to get a percentage of booked desks to available desks. I currently have a measure like this. 

DividePercentageCountMax = IF (

DIVIDE(DataTable[ColumnExample], DataTable2[ColumnExample2]) = BLANK(),

0,

DIVIDE(DataTable[ColumnExample], DataTable2[ColumnExample2])

This works but only when i select one day from the the slicer.

When i try to do this for the full month of January for example my measure now looks like this - 

I want to try and get the last 7 days and last 30 days average of used desks. Not sure how this will work in regards to weekdays too.

Any help would be greatly appreciated!

[amitchandak]

@alexwardeee , names are not matching with name visual to check calculation.

ColumnExample2, ColumnExample should be measures

else you should have measure like

DIVIDE(sum(DataTable[ColumnExample]), sum(DataTable2[ColumnExample2]),0)

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[alexwardeee]

Hi,

Sorry was just trying to be super careful but the names are not important to be honest.

Here is the original measure - 

DividePercentageCountMax = IF (
DIVIDE(DeskBookingReport08122021[CountServiceArea], AllocationData[MaxValue]) = BLANK(),
0,
DIVIDE(DeskBookingReport08122021[CountServiceArea], AllocationData[MaxValue])
)

Says there is an issue -

CountServiceArea is a measure if that might be the issue?

[v-yanjiang-msft]

Hi @alexwardeee ，

According to your description, I create a sample.

Based on this sample, here's the solution.

Create three measures.

CountServiceArea = SUM('DeskBookingReport08122021'[Booked Desks])

MaxValue = SUM('DeskBookingReport08122021'[Avaliable Desks])

Percentage of used desks = DIVIDE([CountServiceArea],[MaxValue],0)

 It will get the correct result, regardless of whether the slicer chooses a value.

If your sample is different from mine, could you please explain it more? what's the formula of CountServiceArea and MaxValue.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[alexwardeee]

Hi @v-yanjiang-msft,

Thanks for the responce.

I tried to use your solution but encoutered a couple of issues which i will note below.

The first issue i had was the 

CountServiceArea = SUM('DeskBookingReport08122021'[Booked Desks])

The booked desk count is actually a string so it errors when it trys to SUM it. 

Secondly the 

MaxValue = SUM('DeskBookingReport08122021'[Avaliable Desks])

Is from it's own table which i have entered in manually as it shows the allocated desks per service area. 

See screenshot - 

When i select multiple dates from the slicer it doesn't seem to sum all these totals.