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 Top2 [PN] with several conditions( as follows,summarize and limit number of count) .
Result of DAX contains unneeded PN.how should I correct it??

■Table 

■DAX

test_topn:=
VAR PN_PO =SUMMARIZE ( 'TEST_','TEST_'[PN],TEST_[PO],'TEST_'[Qty])
VAR PN_PO_Count =GROUPBY ( PN_PO,'TEST_'[PN], "@PO_Count",
　　　　　　　　SUMX ( CURRENTGROUP (), 1 ),"@SUM",sumx(CURRENTGROUP(),'TEST_'[Qty]))
VAR PN_Filtered =FILTER (PN_PO_COUNT, [@PO_Count] > 2 )
VAR Ftd=topn(2,PN_Filtered,[@SUM],DESC)
return calculate(sum('TEST_'[Qty]),KEEPFILTERS(Ftd))


■Result


Best Regards,

  • 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


4 Replies

  • twi1's avatar
    twi1
    Frequent Visitor
    2 years ago

    Hi ohnothimagain ,

    I would suggest this Dax to use for your purpose I have done modifiactions in summarize and group by and I suppose it works, I can not test it as I do not have your table, 
    Dax:
    test_topn :=
    VAR PN_PO =
    SUMMARIZE (
    'TEST_',
    'TEST_'[PN],
    'TEST_'[PO],
    "QtySum", SUM('TEST_'[Qty])
    )
    VAR PN_PO_Count =
    GROUPBY (
    PN_PO,
    'TEST_'[PN],
    "PO_Count", COUNTX ( CURRENTGROUP (), 'TEST_'[PO] ),
    "QtyTotal", SUMX ( CURRENTGROUP (), [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 )
    )

    Please see the response and share with me if it was helpful. You can contact me anytime for further discussion or for changes, if you have another types of requirements here in this task, 
    I look forward to your response, 

    Best regards, 
    Tamar

    • ohnothimagain's avatar
      ohnothimagain
      Helper I
      2 years ago

      twi1 
      Thank you for responding quckly! I copied and tried it right away,but unfortunately
      result was the same..(I'm using PowerPivot for Excel,sorry for inconvenience.)
      alternated formula to confirm the operation as follows was successed,but couldn't apply to "PN_Filtered"
      Could you give me some more solutions..?

      top3:=var top3=topn(3,allselected('TEST_'),'TEST_'[Qty],DESC)
      return calculate(sum('TEST_'[Qty]),keepfilters(top3))

      Best regards,

      • 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