Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

How to get average based on column count

Hi all,

 

How to get average for below table based on count. I want it average based on storage location filter and process code.

 

For example, I only choose storage location PD4 and the average result should be 95.92% every month. I do not want the target result affected by actual running average (another table).

 

 

Storage LocationProcess CodeNMM SegmentMachineTarget
PD4640WASHI640 CH Machine94.000%
PD4640WASHI640 CD Machine94.000%
PD4640WASHI640 CK Machine94.000%
PD4640WASHI640 CG Machine94.000%
PD4640WASHI640 CC Machine94.000%
PD4640EM DC/T640 CB Machine96.900%
PD4640EM DC/T640 CC Machine96.900%
PD4640DC/T640 CB Machine97.500%
PD4640DC/T640 CC Machine97.500%
PD4640EPM Polyester640 CC Machine97.500%
PD4640SPV640 CB Machine97.500%
PD4640TAPE640 CC Machine97.500%
PD4640TAPE640 CD Machine97.500%
PD4400EM DC/T400 WH Machine96.000%
PD4400DC/T400 WH Machine96.000%
PD4400WASHI400 WK Machine94.000%

 

Solution I try but it when there is a month where not all machine is running, the average result is not correct

 

UniqueTarget = AVERAGEX(DISTINCT(SAP_PPT_EV_MaterialDocument_TotalGR[Target]),SAP_PPT_EV_MaterialDocument_TotalGR[Target])

 

UniqueTargetAverage = AVERAGEX(
SUMMARIZE('SAP_PPT_EV_MaterialDocument_TotalGR',
SAP_PPT_EV_MaterialDocument_TotalGR[IssueSlocDesc_LGOBE]
,SAP_PPT_EV_MaterialDocument_TotalGR[Z024_ProcessCode]
,SAP_PPT_EV_MaterialDocument_TotalGR[NMMseg]
,Sheet1[Machine]
, "UniqueTarget",DISTINCT(SAP_PPT_EV_MaterialDocument_TotalGR[Target]))
,[UniqueTarget])

 

 

Regards,

Nuha

 

General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    Anonymous , Try like

    UniqueTargetAverage = AVERAGEX(
    SUMMARIZE('SAP_PPT_EV_MaterialDocument_TotalGR',
    SAP_PPT_EV_MaterialDocument_TotalGR[IssueSlocDesc_LGOBE]
    ,SAP_PPT_EV_MaterialDocument_TotalGR[Z024_ProcessCode]
    ,SAP_PPT_EV_MaterialDocument_TotalGR[NMMseg]
    ,Sheet1[Machine]
    SAP_PPT_EV_MaterialDocument_TotalGR[Target])
    ,[Target])