Lidya has attended:
Power BI Modelling, Visualisation and Publishing course
DAX formula
I have huge raw sales data including products, vendors and the 3 subgroup they belong (product: sink, sales: 450, vendor : x, material->kitchen->home). And every product sale has a vendor. Some of them same some of them different. I want to see top %10 percent of the products for each kitchen group but different vendors (for example if there is 100 products, I want to see 10 top products and the vendors should be different) How I should create clean the data and write the DAX?
RE: 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