★ Products Sorted by Average Delivery Days : EVALUATE SUMMARIZE( Orders, Orders[Product Name],"Avg Delivery Days", AVERAGE(Orders[DELIVERY DAYS]))
★ Top 2 Products by Revenue : DEFINE MEASURE Orders[Revenue] = SUMX(Orders, Orders[UNITS] * RELATED(Products[SALE_PRICE]))
EVALUATE TOPN( 6, SUMMARIZE(Orders, Orders[Product Name], "Revenue", [Revenue]), [Revenue], ASC ) ORDER BY [Revenue] DESC
★ Total Revenue and Total Units in One Row : DEFINE VAR TotalRevenue = SUMX(Orders, Orders[UNITS] * RELATED(Products[SALE_PRICE])) VAR TotalUnits = SUM(Orders[UNITS]) EVALUATE ROW("Total Revenue", TotalRevenue, "Total Units", TotalUnits)
★ Top 2 Most Frequently Ordered Products : EVALUATE TOPN( 2, SUMMARIZE(Orders, Orders[Product Name], "Orders", COUNTROWS(Orders)), [Orders], DESC ) # [ORDERS] IS A COLUMN
★ REVENUE BY COLOR : EVALUATE SUMMARIZE( Products, Products[COLOR], "Revenue", CALCULATE( SUMX(Orders, Orders[UNITS] * RELATED(Products[SALE_PRICE])))) ORDER BY [Revenue] DESC
★ VARIABLE FILTER : DEFINE VAR MaterialFilter = "Steel" EVALUATE FILTER(Products, Products[MATERIAL] = MaterialFilter)
★ PRODUCT WITH HIGHEST REVENUE PER ORDER : EVALUATE TOPN( 2, GROUPBY( Orders, Orders[Product Name], "AvgRevenue", AVERAGEX(CURRENTGROUP(), Orders[UNITS] * RELATED(Products[SALE_PRICE]) ) ), [AvgRevenue], DESC )
★ Compare Sale and Cost Price by Product : EVALUATE SELECTCOLUMNS( Products, "Product", Products[NAME], "Sale Price", Products[SALE_PRICE], "Cost Price", Products[COST_PRICE], "Margin", Products[SALE_PRICE] - Products[COST_PRICE] ) ORDER BY [Margin] DESC
★Revenue by material and color : EVALUATE SUMMARIZE( Products, Products[MATERIAL], Products[COLOR], "Revenue", CALCULATE(SUMX(Orders, Orders[UNITS] * RELATED(Products[SALE_PRICE])))) ORDER BY [Revenue] DESC
★ List products never ordered : EVALUATE EXCEPT( SUMMARIZE(Products, Products[NAME]), SUMMARIZE(Orders, Orders[Product Name]) )
EXCEPT works with tables, not scalar values.
No comments:
Post a Comment