Forum Discussion

anupambit1797's avatar
anupambit1797
Iron Contributor
May 09, 2025
Solved

Data extraction from Pivot

Dear Experts,                    Greetings! I have a data like below(Worksheet attached too):-  Column A(having rntis), B (having slots) and C having the transmission(DL_1_x) Out of this , fo...
BI & Data Analysis
Charting
excel
Formulas and Functions
  • PeterBartholomew1's avatar
    PeterBartholomew1
    May 12, 2025

    This is what I got from the Excel formula

    = PIVOTBY(Rnti, DCI_Slot, SIGN(ISTEXT(DCI_Info)), PERCENTOF,,0,,0,,,1)

    with a bit of conditional formatting

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
May 12, 2025

This is what I got from the Excel formula

= PIVOTBY(Rnti, DCI_Slot, SIGN(ISTEXT(DCI_Info)), PERCENTOF,,0,,0,,,1)

with a bit of conditional formatting

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    May 12, 2025

    Looks nice. As variant without conditional formatting

    =LET(
  Mark,    UNICHAR(9679),
  noMark,  UNICHAR(9675),
  Cleaned, FILTER( Table1, ISNUMBER(Table1[Rnti]) ),
  Rnti,    CHOOSECOLS( Cleaned, 1),
  DCI,     CHOOSECOLS( Cleaned, 2),
  slots,   SORT( UNIQUE(DCI) ),
  byRnti,  PIVOTBY(Rnti, DCI, DCI, LAMBDA(a,v, COUNTA(a) ),,0,,0),
  bySlots, TRANSPOSE( PIVOTBY(DCI, Rnti, DCI, LAMBDA(a,v, COUNTA(v)/ROWS(slots)  ),,0,,0) ),
  data,    IF(DROP(byRnti,1,1) < 0.7*DROP(bySlots,1,1), Mark, noMark ),
  HSTACK(
     VSTACK("Rnti/Slot", DROP(TAKE(byRnti,,1),1)),
     VSTACK( TOROW(slots), data ) )
)

    Compare with PivotTable

Resources