Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Memory problem with TopN Measure

Hello I have a table with warehouse inventory information and a table with sales information. I'm trying to get a count of the number of the top 5 products per warehouse. Please see the example belo...
Need Help
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    Hi Anonymous ,

     

    We create three optimized measures and you can try them and verify which one can work.

     

    Measure_1 = 
VAR Top5 =
    CALCULATETABLE (
        DISTINCT ( 'Sales'[Product] ),
        FILTER (
            'Sales',
            RANKX ( GROUPBY ( 'Sales', 'Sales'[Product] ), CALCULATE ( [Sales Count] ) ) <= 5
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Inventory'[Count of Products] ),
        'Inventory'[Product] IN Top5
)

     

    Measure_2 = 
VAR Top5 =
    CALCULATETABLE (
        DISTINCT ( 'Sales'[Product] ),
        TOPN (
            5,
            SUMMARIZE ( 'Sales', 'Sales'[Product] ),
            CALCULATE ( [Sales Count] )
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Inventory'[Count of Products] ),
        'Inventory'[Product] IN Top5
)

     

    Measure_3 = 
VAR t =
    SUMMARIZE (
        'Sales',
        'Sales'[Product],
        "Temp", CALCULATE ( [Sales Count] )
    )
VAR Top5 =
    SELECTCOLUMNS ( TOPN ( 5, t, [Temp] ), "Produce", [Product] )
RETURN
    CALCULATE (
        SUM ( 'Inventory'[Count of Products] ),
        'Inventory'[Product] IN Top5
)

     

    The result like this,

     

     

    BTW, pbix as attached.

     

    Best regards,

     

    Community Support Team _ zhenbw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-zhenbw-msft's avatar
v-zhenbw-msft
Community Support
6 years ago

Hi Anonymous ,

 

We create three optimized measures and you can try them and verify which one can work.

 

Measure_1 = 
VAR Top5 =
    CALCULATETABLE (
        DISTINCT ( 'Sales'[Product] ),
        FILTER (
            'Sales',
            RANKX ( GROUPBY ( 'Sales', 'Sales'[Product] ), CALCULATE ( [Sales Count] ) ) <= 5
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Inventory'[Count of Products] ),
        'Inventory'[Product] IN Top5
)

 

Measure_2 = 
VAR Top5 =
    CALCULATETABLE (
        DISTINCT ( 'Sales'[Product] ),
        TOPN (
            5,
            SUMMARIZE ( 'Sales', 'Sales'[Product] ),
            CALCULATE ( [Sales Count] )
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Inventory'[Count of Products] ),
        'Inventory'[Product] IN Top5
)

 

Measure_3 = 
VAR t =
    SUMMARIZE (
        'Sales',
        'Sales'[Product],
        "Temp", CALCULATE ( [Sales Count] )
    )
VAR Top5 =
    SELECTCOLUMNS ( TOPN ( 5, t, [Temp] ), "Produce", [Product] )
RETURN
    CALCULATE (
        SUM ( 'Inventory'[Count of Products] ),
        'Inventory'[Product] IN Top5
)

 

The result like this,

 

 

BTW, pbix as attached.

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thank you! It works with the following code:

     

    VAR top5 =
    SELECTCOLUMNS (
        TOPN (
            5,
            SUMMARIZE ( 'Sales', 'Sales'[Product], "Count", [Sales Count] ),
            [Count]
        ),
        "Product", 'Sales'[Product]
    )
RETURN
    CALCULATE (
        SUM ( 'Inventory'[Count of Products] ),
        'Inventory'[Product] IN top5
    )