Measure to create results inside a pivot table in excel

[AmeenVanakar]

Hi,

I am trying to arrive at a final average utilization for multiple services & so far I have been able to do this manually using pivot tables but I need a measure formula that could solve each of these in excel. Here's a screenshot of the final 4 results I need.

I am also attaching the excel sheet that I worked on. Please note that the data is loaded to data model.

Here is the link to access the excel file

https://1drv.ms/x/c/d89d89d28c3ea2f6/EfFLRuvz6hpJl8i4cO9chw4BYaEVyVAgG_VMdrEA6KbR9w?e=7v4Qeq 

Thank you in advance.

[v-mdharahman]

Hi @AmeenVanakar,

Thanks for reaching out to the Microsoft fabric community forum.

From what you’ve described, the final result should be “% average of all the daily % occupancy by individual services”. You can do this directly in the Data Model with two quick additions:

* Current Week column (in your Date table)

IsCurrentWeek =
VAR RowWeek = WEEKNUM('Date'[Date], 2)
VAR TodayWeek = WEEKNUM(TODAY(), 2)
RETURN
IF( RowWeek = TodayWeek && YEAR('Date'[Date]) = YEAR(TODAY()), "Yes", "No")

* Measure for the final average, replace column names with your actual ones.

AverageOfServiceDailyAverages :=
AVERAGEX(
VALUES( 'Service'[ServiceID] ),
CALCULATE( AVERAGE( 'Fact'[PctOccupancy] ) )
)

To use in pivot put "AverageOfServiceDailyAverages" in Values. Then add your Source field in Columns, and DayType / IsCurrentWeek in Rows or Filters as needed. Now format the measure as a percentage. This will give you the same result as your manual pivot, but calculated dynamically in the model. If results still don’t align, please share a few fact table rows so we can check the aggregation logic.

I would also take a moment to thank @FBergamaschi, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.

[FBergamaschi]

Thanks for this well presented post

Can you explain where the data in first two columns come from ("all dates"/"weekday"/"weekend" and the other column "all sources" / "online" /"onsties")?

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[FBergamaschi]

Meanwhile you find here a first draft

https://drive.google.com/drive/folders/1Hh_YJboHKRvenOD-ZurdSunVIaNKH2vn?usp=sharing

You need a column for the current week to complete what you are looking for, is what I did enough or you need that too?

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[AmeenVanakar]

Hi Francesco,

Thanks for attemting to solve this & sharing the formula. However the results dont seem to match with the exact expected results as shown in the excel file. The end results should be the %average of all the daily %occupancy by individual services.

Regarding the current week column, how can I do that? is there a formula I can add in a custom column that will give me current week yes/no in the data model?

Thanks.