Top N & filter on mulliple criteria

[kfschaefer]

Top 1 Item for Package = 
VAR
    RankingContext = VALUES(QRY_ITEM[Description])
RETURN
CALCULATE (QRY_ITEM[QTY],
    TOPN(1, ALL(QRY_ITEM[Description]), [QTY]),
    RANKINGCONTEXT )

I am attemting to create a list of unique Items,Description that are the Top 1 per Package Type & per MthYYYYY.  The above measure is having issues with the QTY portion of the code.  What am I missing.

I tried to use the code from the following link.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Top-1-per-category/m-p/225700#M49D

Sample Data:

https://www.dropbox.com/s/sxu2if509e8k3wd/kfsSampleData1.xlsx?dl=0

[v-danhe-msft]

Hi @kfschaefer,

Form the link you have offered, it seemed that the QRY_ITEM[QTY] should be a measure. You could use below formula to create the measure of  QRY_ITEM[QTY]:

Measure Qty = CALCULATE(SUM(QRY_ITEM[Qty]))

Regards,

Daniel He

[kfschaefer]

Thanks for the response, however, how would I use this measure to calculate the top 1 item(description) per each type and monthly date range.

[v-danhe-msft]

Hi @kfschaefer,

Could you have tried with the measure you have mentioned before:

Top 1 Item for Package1 = 
VAR
    RankingContext = VALUES(QRY_ITEM[Description])
RETURN
CALCULATE (QRY_ITEM[QTY1],
    TOPN(1, ALL(QRY_ITEM[Description]), [QTY1]),
    RANKINGCONTEXT )

I have tried with this measure and could get this result:

I suggest you try again.

Regards,

Daniel He

[kfschaefer]

Thank you for your assistance, I try to implement your Measure statement, however, I need to modify to handle multiple filters (by Invoice Date and PackageType)  These are the groupings.  Not sure if these should be filters or a group by.  How do I implement group by into the measure?

Here is what I have so far:

Top 1 Item for Package1 = 
VAR
    RankingContext = VALUES(QRY_ITEM[Description])
RETURN
CALCULATE (SUM(QRY_ITEM[Qty]),
    TOPN(1, ALL(QRY_ITEM[Description]),SUM(QRY_ITEM[Qty]),DESC)
    RankingContext))

here is the error msg:  

"The syntax for 'RankingContext' is incorrect. (DAX(VAR RankingContext = VALUES(QRY_ITEM[Description])RETURNCALCULATE (SUM(QRY_ITEM[Qty]), TOPN(1, ALL(QRY_ITEM[Description]),SUM(QRY_ITEM[Qty]),DESC) RankingContext))))."

I will still be researching this issue and how to include multiple filters.  Any assistance you can offer is greatly appreciated.

Thanks,

Karen