SOLVED

I want feedack percentage if pending than 0% if accepted than 100% if 1 pending 1 accept than 50%

%3CLINGO-SUB%20id%3D%22lingo-sub-3322075%22%20slang%3D%22en-US%22%3ERe%3A%20I%20want%20feedack%20percentage%20if%20pending%20than%200%25%20if%20accepted%20than%20100%25%20if%201%20pending%201%20accept%20than%2050%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3322075%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1209178%22%20target%3D%22_blank%22%3E%40aayushmanmishra%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20first%20calculation%20you%20describe%20would%20appear%20to%20be%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20Pending%20%2F%20(Accept%2BPending)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThe%20conversion%20to%20pivot%20table%20normalised%20form%20is%20a%20major%20task%20by%20comparison%2C%26nbsp%3B%20Using%20365%20beta%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20LET(%0A%20%20%20%20%20%20a%2C%20HSTACK(Advisor%2C%20Accept%2C%20%20IF(Accept%2CacceptHdr))%2C%0A%20%20%20%20%20%20p%2C%20HSTACK(Advisor%2C%20Pending%2C%20IF(Pending%2CpendingHdr))%2C%0A%20%20%20%20%20%20combined%2C%20VSTACK(FILTER(a%2CAccept%26gt%3B0)%2CFILTER(p%2CPending))%2C%0A%20%20%20%20%20%20SORT(combined)%0A%20%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3322157%22%20slang%3D%22en-US%22%3ERe%3A%20I%20want%20feedack%20percentage%20if%20pending%20than%200%25%20if%20accepted%20than%20100%25%20if%201%20pending%201%20accept%20than%2050%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3322157%22%20slang%3D%22en-US%22%3Egreat%20thanks%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3321934%22%20slang%3D%22en-US%22%3EI%20want%20feedack%20percentage%20if%20pending%20than%200%25%20if%20accepted%20than%20100%25%20if%201%20pending%201%20accept%20than%2050%25%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3321934%22%20slang%3D%22en-US%22%3E%3CTABLE%20width%3D%22384%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2292%22%3EAdvisor%20Name%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EAccept%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EPending%3C%2FTD%3E%3CTD%20width%3D%2278%22%3EGrand%20Total%3C%2FTD%3E%3CTD%20width%3D%2286%22%3EPercentage%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EA%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%3F%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EB%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%3F%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EC%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%3F%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ed%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%3F%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ee%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%3F%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ef%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%3F%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eg%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%3F%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eh%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%3F%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ei%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%3F%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ej%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E%3F%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ek%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E%3F%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3El%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%3F%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Em%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%3F%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EGrand%20Total%3C%2FTD%3E%3CTD%3E7%3C%2FTD%3E%3CTD%3E8%3C%2FTD%3E%3CTD%3E15%3C%2FTD%3E%3CTD%3Eaverage%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3Esecondly%20this%20data%20is%20in%20pivot%20form%20than%20how%20to%20get%20this%20percentage%20value%20using%20pivot%20options%20only.%20instead%20of%20using%20formula%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3321934%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor
Advisor NameAcceptPendingGrand TotalPercentage
A 11?
B 11?
C1 1?
d1 1?
e1 1?
f 11?
g 11?
h1 1?
i 11?
j2 2?
k 22?
l1 1?
m 11?
Grand Total7815average

secondly this data is in pivot form than how to get this percentage value using pivot options only. instead of using formula

2 Replies
best response confirmed by aayushmanmishra (Occasional Contributor)
Solution

@aayushmanmishra 

The first calculation you describe would appear to be 

= Pending / (Accept+Pending)

The conversion to pivot table normalised form is a major task by comparison,  Using 365 beta

= LET(
      a, HSTACK(Advisor, Accept,  IF(Accept,acceptHdr)),
      p, HSTACK(Advisor, Pending, IF(Pending,pendingHdr)),
      combined, VSTACK(FILTER(a,Accept>0),FILTER(p,Pending)),
      SORT(combined)
  )