Forum Discussion

ohnothimagain's avatar
ohnothimagain
Helper I
2 years ago
Solved

TOPN with variable table

Hello everyone,　   (Special thanks to OwenAuger ,privious time) I've been stuck again in this week..this might be basic but I couldn't find a way to solve it.. I have a table below,want to get To...
  • twi1's avatar
    twi1
    2 years ago

    Maybe, you can try these measures, any of them:test_topn :=
    VAR PN_PO =
    SUMMARIZE (
    'TEST_',
    'TEST_'[PN],
    'TEST_'[PO],
    'TEST_'[Qty]
    )
    VAR PN_PO_Count =
    ADDCOLUMNS (
    SUMMARIZE (
    PN_PO,
    'TEST_'[PN]
    ),
    "@PO_Count", CALCULATE ( COUNT ( 'TEST_'[PO] ) ),
    "@SUM", CALCULATE ( SUM ( 'TEST_'[Qty] ) )
    )
    VAR PN_Filtered =
    FILTER (
    PN_PO_Count,
    [@PO_Count] > 2
    )
    VAR TopPNs =
    TOPN (
    2,
    PN_Filtered,
    [@SUM],
    DESC
    )
    RETURN
    CALCULATE (
    SUM ( 'TEST_'[Qty] ),
    KEEPFILTERS ( TopPNs )
    )
    or that one:
    test_topn :=
    VAR top2 =
    TOPN (
    2,
    SUMMARIZE (
    ALLSELECTED('TEST_'),
    'TEST_'[PN],
    "QtySum", SUM ( 'TEST_'[Qty] ),
    "PO_Count", COUNT ( 'TEST_'[PO] )
    ),
    [QtySum],
    DESC
    )
    RETURN
    CALCULATE (
    SUM ( 'TEST_'[Qty] ),
    KEEPFILTERS ( top2 ),
    FILTER (
    ALLSELECTED('TEST_'),
    [PO_Count] > 2
    )
    )

    or that one:test_topn :=
    VAR PN_PO =
    SUMMARIZE (
    'TEST_',
    'TEST_'[PN],
    'TEST_'[PO],
    "QtySum", SUM('TEST_'[Qty])
    )
    VAR PN_PO_Count =
    SUMMARIZE (
    PN_PO,
    'TEST_'[PN],
    "PO_Count", COUNT('TEST_'[PO]),
    "QtyTotal", SUM('TEST_'[QtySum])
    )
    VAR PN_Filtered =
    FILTER (PN_PO_Count, [PO_Count] > 2)
    VAR Top2 =
    TOPN(2, PN_Filtered, [QtyTotal], DESC)
    RETURN
    CALCULATE (
    SUM('TEST_'[Qty]),
    KEEPFILTERS(Top2)
    ) Try each of them maybe this gives you a way to better solution for power pivot in excel


twi1's avatar
twi1
Frequent Visitor
2 years ago

Maybe, you can try these measures, any of them:test_topn :=
VAR PN_PO =
SUMMARIZE (
'TEST_',
'TEST_'[PN],
'TEST_'[PO],
'TEST_'[Qty]
)
VAR PN_PO_Count =
ADDCOLUMNS (
SUMMARIZE (
PN_PO,
'TEST_'[PN]
),
"@PO_Count", CALCULATE ( COUNT ( 'TEST_'[PO] ) ),
"@SUM", CALCULATE ( SUM ( 'TEST_'[Qty] ) )
)
VAR PN_Filtered =
FILTER (
PN_PO_Count,
[@PO_Count] > 2
)
VAR TopPNs =
TOPN (
2,
PN_Filtered,
[@SUM],
DESC
)
RETURN
CALCULATE (
SUM ( 'TEST_'[Qty] ),
KEEPFILTERS ( TopPNs )
)
or that one:
test_topn :=
VAR top2 =
TOPN (
2,
SUMMARIZE (
ALLSELECTED('TEST_'),
'TEST_'[PN],
"QtySum", SUM ( 'TEST_'[Qty] ),
"PO_Count", COUNT ( 'TEST_'[PO] )
),
[QtySum],
DESC
)
RETURN
CALCULATE (
SUM ( 'TEST_'[Qty] ),
KEEPFILTERS ( top2 ),
FILTER (
ALLSELECTED('TEST_'),
[PO_Count] > 2
)
)

or that one:test_topn :=
VAR PN_PO =
SUMMARIZE (
'TEST_',
'TEST_'[PN],
'TEST_'[PO],
"QtySum", SUM('TEST_'[Qty])
)
VAR PN_PO_Count =
SUMMARIZE (
PN_PO,
'TEST_'[PN],
"PO_Count", COUNT('TEST_'[PO]),
"QtyTotal", SUM('TEST_'[QtySum])
)
VAR PN_Filtered =
FILTER (PN_PO_Count, [PO_Count] > 2)
VAR Top2 =
TOPN(2, PN_Filtered, [QtyTotal], DESC)
RETURN
CALCULATE (
SUM('TEST_'[Qty]),
KEEPFILTERS(Top2)
) Try each of them maybe this gives you a way to better solution for power pivot in excel


ohnothimagain's avatar
ohnothimagain
Helper I
2 years ago

thank you so much for considering some patterns!
I'm afraid to say that each of them was unsucess,but one of my trial seems to be successed.I have no idea why it works... anyway,I deal with it as solved.

test_topn:=
VAR PN_PO =
SUMMARIZE (allselected('TEST_'),