topic Sum of Units for Top5 items per Category by Sales Dollars in DAX Commands and Tips

Sum of Units for Top5 items per Category by Sales Dollars

gshroff — Thu, 10 Mar 2022 22:17:21 GMT

I am tyring to return a table with the sum of the units sold of the top 5 items by sales per category.

The resulting table should look like:

Category 1	Sum of Units of top5 items	Sum of Sales of top5 items
Category 2	Sum of Units of top5 items	Sum of Sales of top5 items

I have Item[Category] and Item[UPC] in the same dimension and Units and Sales in an account dimension with a [Units] Measure = Calculate(Sum(Fact[Amount], Filter(All(Accounts), Accounts[Name] = "Units"). The sales measure is written the similarly.
I have tried,

EVALUATE
SUMMARIZECOLUMNS (
'Item'[Department],
'Item'[UPC],
"sales", [Sales] ,

"units", [Units],
    "Rank",
        IF (
            ISINSCOPE ( 'Item'[UPC] ),
            RANKX (
                CALCULATETABLE (
                    VALUES ( 'Item'[UPC] ),
                    ALLSELECTED ( 'Item'[UPC])
                ),
[Sales],
                ,
                0,
                DENSE
            )
        )
)

This gives me the rank, sales and units per upc for each department. I am struggling with how to only return the top 5 upc's with nonzero sales and the sum of Sales and Units for each department without UPC's being displayed.

Any guidance the community could give would be appreciated.

Re: Sum of Units for Top5 items per Category by Sales Dollars

amitchandak — Fri, 11 Mar 2022 03:15:40 GMT

gshroff , I thnk this one should do

Top 5 =calculate([Sum sales measure], TOPN(5,allselected(Table[Item]),[Sum sales measure],DESC), values(Table[Item]))

if not refer

https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/

Re: Sum of Units for Top5 items per Category by Sales Dollars

Anonymous — Tue, 15 Mar 2022 06:55:30 GMT

Hi gshroff ,

I think your table should look like as below after SUMMARIZECOLUMNS.

Try my code to get a table look like as your mentioned.

Result Table = VAR _SUMMARIZECOLUMN = SUMMARIZECOLUMNS ( 'Item'[Department], 'Item'[UPC], "sales", [Sales], "units", [Units], "Rank", IF ( ISINSCOPE ( 'Item'[UPC] ), RANKX ( CALCULATETABLE ( VALUES ( 'Item'[UPC] ), ALLSELECTED ( 'Item'[UPC] ) ), [Sales], , 0, DENSE ) ) ) RETURN SUMMARIZE ( _SUMMARIZECOLUMN, [Department], "Sum of Units of top5 items", SUMX ( FILTER ( _SUMMARIZECOLUMN, [Rank] <= 5 && [Sales] <> 0 && [Department] = EARLIER ( [Department] ) ), [Units] ), "Sum of Sales of top5 items", SUMX ( FILTER ( _SUMMARIZECOLUMN, [Rank] <= 5 && [Sales] <> 0 && [Department] = EARLIER ( [Department] ) ), [Sales] ) )

Result is as below.

Best Regards,
Rico Zhou

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