Calculate grand average from raw average based on time and date filter slisers

[icrishanka]

Hi,

Please support on following issues,

Background : I am working on calculating grand average from raw average which come based sliser selection time renge(Specific) and Date. 

Objective : 1. get grand average from Total PA (Total PA change with filter time rage and Date), want to get single value (just show in following table).

2. Calculate grand averge except last obervation after filter range and date (example : except last raw 9/10/2023 4:00:00 PM

)

3. based on this calculate Control chart. 

Issues is : If I get any missing raw, then I get incorrect result (I found this). 

Refere image 1 : Correct result.

Refer image 2 : Incorrect result with missing raw. 

Value 6.72% instead 7.92%.

Using following measures : 

Total PA =

CALCULATE(

    [Total_Defect]/[Total_Sample]

)

AVG =

VAR _AVG =

    CALCULATE(AVERAGEX(SUMMARIZE('06.1._Transaction Time','06.1._Transaction Time'[Time Slot],"ToAVG",[Total PA]),[ToAVG])

)

RETURN _AVG

AVG_3 =

VAR G_AVG = CALCULATE(AVERAGEX(SUMMARIZE('05.Calender','05.Calender'[Date],"_PA",[AVG]),[_PA]))

RETURN G_AVG

If any one one have any suggestion, please share to me. (Need to calculate control chart excluding last summarized raw as above image). 

Thank you

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[icrishanka]

Hi Ibendlin,

Please find following GooglDrive file (if any issues let me know),

Attached : .pbi (see modeling and allcode)

2 Excel file : 1> Raw data

 2> Expected results calculations.

https://drive.google.com/drive/folders/1A1xRARDPxLVfMA9tu2cQtKVfviA-REPl?usp=share_link

Thank you very much for your support .

Thanks!

[lbendlin]

Are you sure your timeslots work as designed?

Change your data model slightly

Now, if you want to calculate the averages over only the entries with data you'll have to ask someone else to help you. I don't subscribe to that school of thought.

[icrishanka]

Thank you Ibendlin,

I got a solution without chaning data model. Yup, I think I can manage rest of the parts.

Thank you.