Forum Discussion

anupambit1797's avatar
anupambit1797
Steel 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 , for each rnti , I need to populate like in the Result, ( I gave 2 examples for rnti's 

13648
11808

So, for rnti 13648, transmissions are low in slots 3 & 10, so put "x" in the slots 3 and 10, 

Similarly , for rnti - 11808, we have less transmissions in slots 0/8/18 so in below Result we see "x" in the corresponding slots.

Can you please share some Logic/formula to achieve this?

Also, apart from x, if we can also have some data like how much %age loss per rnti per slot for each slots ?

Thanks in Advance,

Br,

Anupam

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

5 Replies

Sort By
  • 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
      MVP
      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

  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    May 10, 2025

    With adding data to data model we may add couple of measures

    Count:=COUNTROWS( Table1 )

Mark:=VAR data =
    ADDCOLUMNS (
        Table1,
        "count", CALCULATE ( COUNTA ( Table1[DCI Info DCI Format] ) )
    )
VAR maxInRow =
    CALCULATE (
        MAXX ( VALUES ( Table1[DCI Info Slot] ), [Count] ),
        ALLEXCEPT ( Table1, Table1[Rnti] )
    )
RETURN
    IF ( [Count] < 0.7 * maxInRow, "x", "" )

    PivotTable with latest shows

  • Kidd_Ip's avatar
    Kidd_Ip
    MVP
    May 10, 2025

    i thought you may identify the threshold for low transmission (e.g., using AVERAGE(), MEDIAN(), or PERCENTILE()), then apply conditional formatting or formulas to mark “x” in slots that fall below the threshold and compute percentage loss relative to the highest transmission for each RNTI.

Resources