topic Re: Need help with DAX logic for dynamic sales value banding (50% Green, 20% Yellow, 30% Red) in DAX Commands and Tips

Need help with DAX logic for dynamic sales value banding (50% Green, 20% Yellow, 30% Red)

Anandshah — Mon, 27 Oct 2025 18:35:53 GMT

Hi everyone,

I have two tables in my Power BI model:

1. Fact table – contains columns like Country, State, City, Pincode, Product Name, Category, etc.

2. Sales table – contains Sales Value and Product Name,date,net profit

I want to create a logic based on Sales Value bands:

Top 50% → Green

Next 20% → Yellow

Remaining 30% → Red

Here’s the requirement:
When I select any filter such as Country or Pincode, I only want to calculate the max sales value for that selected filter and not for all filters combined.

For example, if I select a specific Pincode, it filters 100 rows from my 10,000-row dataset. Among those 100 filtered rows, I want the top 50% to fall under Green, next 20% under Yellow, and the last 30% under Red dynamically based on filter selection.

I already tried writing a DAX measure, but it’s not giving the expected result. The calculation seems to ignore the filter context and applies across the entire dataset.

Could anyone please help me with the correct DAX logic to achieve this?

Thanks in advance!

Re: Need help with DAX logic for dynamic sales value banding (50% Green, 20% Yellow, 30% Red)

GeraldGEmerick — Mon, 27 Oct 2025 19:12:51 GMT

Anandshah It would be very helpful if you included sample data for this. Also, it sounds like perhaps you are missing a relationship between your two tables. Finally, your Sales table should be classified as your "fact" table since it contains numbers (metrics) while your other table sounds like a Product dimension table.

Re: Need help with DAX logic for dynamic sales value banding (50% Green, 20% Yellow, 30% Red)

Kedar_Pande — Tue, 28 Oct 2025 13:01:08 GMT

Anandshah

Sales Band =
VAR TotalSales = SUM(Sales[Sales Value])
VAR FilteredTable = ALLSELECTED(Sales)
VAR RankBySales = RANKX(FilteredTable, [Sales Value],, DESC, DENSE)
VAR TotalRows = COUNTROWS(FilteredTable)
VAR Percentile = RankBySales / TotalRows
RETURN
SWITCH(TRUE(),
Percentile <= 0.5, "Green",
Percentile <= 0.7, "Yellow",
"Red"
)

This calculates percentiles within your current filter context, not the entire dataset.

If this answer helped, please click Kudos or mark as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande

Re: Need help with DAX logic for dynamic sales value banding (50% Green, 20% Yellow, 30% Red)

Anandshah — Thu, 30 Oct 2025 05:08:48 GMT

No, this isn’t what I meant I’ll explain it in detail here.

I have two tables in my Power BI model:
1️⃣ Fact Table – contains columns like Country, State, City, Pincode, Product Name, Category, etc.
2️⃣ Sales Table – contains Sales Value, Store, Date, Net Profit, Store Size, and Employee Number.

My Requirement
I want to create a logic based on Sales Value bands as follows:

Top 50% → Green

Next 20% → Yellow

Remaining 30% → Red

The goal is to plot these bands dynamically based on the group or filters applied, and then superimpose the store-wise sales value over the bands.

For example:
If I have store-wise sales data across multiple states and I select California → Los Angeles, out of thousands of data points only 100 stores may appear.
Now, for those 100 stores:

The top 50% (highest sales) should form the Green band,

The next 20% should form the Yellow band,

The remaining 30% should form the Red band.

Based on these, I’ll have minimum and maximum values for each band (Green, Yellow, Red), which will define my band zones.

Additional Requirement
Once I get these bands using DAX, I also want to:

Superimpose the Sales Value of any particular store on top of these bands — so I can easily see where that store lies.

Ideally, I’d like to compare multiple stores at once (e.g., two or more) to visualize how they perform relative to the defined bands.

The important part is that the bands must be redefined dynamically based on the filters applied.
For example, if I apply an additional filter like “Employee Number > 3”, then the bands should recalculate automatically for the filtered dataset.

As of now, I can successfully plot the bands, but I’m unable to superimpose the Sales Value over them.
I’d really appreciate your support or guidance on how to achieve this part.

Re: Need help with DAX logic for dynamic sales value banding (50% Green, 20% Yellow, 30% Red)

v-aatheeque — Mon, 03 Nov 2025 07:25:15 GMT

Hi Anandshah

To validate the logic, please share a sample dataset from the relevant tables in a usable format (not a screenshot), covering the issue completely and excluding any sensitive or unrelated information.

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Re: Need help with DAX logic for dynamic sales value banding (50% Green, 20% Yellow, 30% Red)

v-aatheeque — Thu, 06 Nov 2025 09:29:37 GMT

Hi Anandshah

To better reproduce and troubleshoot your scenario, we kindly request a small sample dataset/file. Please share it at your convenience, as this will help us provide a more accurate and tailored solution.

Re: Need help with DAX logic for dynamic sales value banding (50% Green, 20% Yellow, 30% Red)

v-aatheeque — Mon, 10 Nov 2025 13:31:57 GMT

Hi Anandshah

Have you had a chance to look through the responses shared earlier?
looking forward for your response ?