Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

dax formula

ResolvedVersion 365

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

Wed 4 Sep 2024: Automatically marked as resolved.

 

Training courses

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.

Power BI tip:

Optimize Report Performance

Pay attention to report performance by optimizing your data model and report design. Use the Performance Analyzer tool to identify bottlenecks in your report and improve loading times. Techniques such as using summarisation, avoiding unnecessary visuals, and optimizing DAX queries contribute to a faster and more responsive report.

View all Power BI hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.09 secs.