DAX formula

Hi Lidya

Thank you for using the forum to ask a question.
Please start by following the steps below

Step 1: Clean the Data
Import Data: Load your raw sales data into Power BI.
Transform Data: Use Power Query to clean and structure your data.
Remove Duplicates: Ensure there are no duplicate entries.
Split Columns: If your subgroup data is in a single column (e.g., material->kitchen->home), split it into separate columns for better analysis.
Filter Data: Remove any irrelevant data or outliers that might skew your analysis

Step 2: Create Calculated Columns
Rank Products by Sales: Create a calculated column to rank products within each kitchen group by sales.
Rank =
RANKX(
FILTER(
SalesData,
SalesData[KitchenGroup] = EARLIER(SalesData[KitchenGroup])
),
SalesData[Sales],
,
DESC,
DENSE
)

Identify Top 10% Products: Create a calculated column to flag the top 10% products within each kitchen group.
Top10Percent =
IF(
SalesData[Rank] <=
ROUNDUP(
COUNTROWS(
FILTER(
SalesData,
SalesData[KitchenGroup] = EARLIER(SalesData[KitchenGroup])
)
) * 0.1,
0
),
1,
0
)

Step 3: Create a Measure to Filter Different Vendors
Distinct Vendors: Create a measure to count distinct vendors for the top 10% products.
DistinctVendors =
CALCULATE(
DISTINCTCOUNT(SalesData[Vendor]),
SalesData[Top10Percent] = 1
)

Step 4: Visualise the Data
Create a Table or Matrix: Use a table or matrix visual to display the top 10% products and their vendors.
Apply Filters: Apply filters to ensure only the top 10% products with distinct vendors are shown.
Example Visualisation
Columns: Product, Sales, Vendor, Rank, Top10Percent
Filters: Top10Percent = 1, DistinctVendors

This approach ensures you can see the top 10% products for each kitchen group with different vendors.

Kind regards

Richard

STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector