topic Re: Power BI Modeling Question – Dynamic Common Sample + Buckets + Weighted KPIs in DAX Commands and Tips

Power BI Modeling Question – Dynamic Common Sample + Buckets + Weighted KPIs

atziovara — Fri, 24 Apr 2026 13:18:32 GMT

🔹 Data Structure

I am working with financial data for companies across multiple years (2020–2025).

The dataset includes:

Company (VAT Number)
Financial Year
Region
Sector
Financial metrics:
- Net Sales
- EBITDA
- Total Assets

Example (raw wide format – simplified)

VAT Year Region Sector Net Sales EBITDA Total Assets

A	2020	Attica	Retail	500,000	50,000	1,000,000
A	2022	Attica	Retail	700,000	80,000	1,200,000
A	2023	Attica	Retail	0	0	1,100,000
B	2022	Crete	Industry	2,000,000	200,000	3,000,000
B	2023	Crete	Industry	3,000,000	300,000	3,500,000
C	2021	Attica	Services	BLANK	BLANK	BLANK
C	2022	Attica	Services	5,000,000	400,000	6,000,000
D	2022	Thessaly	Retail	12,000,000	1,000,000	15,000,000
D	2023	Thessaly	Retail	13,000,000	1,200,000	16,000,000
E	2020	Crete	Services	800,000	60,000	900,000

🔹 Data Transformation

I have unpivoted the financial columns, so the data in Power BI looks like this:

VAT Year Region Sector Attribute Value

A	2022	Attica	Retail	Net Sales	700,000
A	2022	Attica	Retail	EBITDA	80,000
A	2022	Attica	Retail	Total Assets	1,200,000
…	…	…	…	…	…

🔹 Base Measures

Net Sales =CALCULATE(SUM('Page1'[Value]),'Page1'[Attribute] = "Net Sales")EBITDA =CALCULATE(SUM('Page1'[Value]),'Page1'[Attribute] = "EBITDA")Total Assets =CALCULATE(SUM('Page1'[Value]),'Page1'[Attribute] = "Total Assets")

🔹 KPIs

Weighted Total Asset Turnover = Net Sales / Total Assets
Weighted EBITDA Margin = EBITDA / Net Sales

🎯 REQUIREMENTS

I want the report to support three independent filtering mechanisms:

1️⃣ Standard Filtering (WORKS)

I can already filter:

Financial Year
Region
Sector

All accounts and KPIs respond correctly.

2️⃣ Sales Buckets (WORKS)

I implemented dynamic sales segmentation:

Step 1 – Bucket definition

Sales Bucket =SWITCH(TRUE(),[Net Sales] < 1000000, "<1M",[Net Sales] >= 1000000 && [Net Sales] < 10000000, "1–10M",[Net Sales] > 10000000, ">10M",[Net Sales] <= 10000000, "<=10M")

Step 2 – Disconnected table

Sales Buckets =DATATABLE("Bucket", STRING,{{"<1M"},{"1–10M"},{">10M"},{"<=10M"}})

Step 3 – Selection logic (multi-select compatible)

Selected Bucket =
VAR NetSales = [Net Sales]
RETURN
IF(
SUMX(
VALUES('Sales Buckets'[Bucket]),
SWITCH(
TRUE(),
'Sales Buckets'[Bucket] = "<1M" && NetSales < 1000000, 1,
'Sales Buckets'[Bucket] = "1–10M" && NetSales >= 1000000 && NetSales < 10000000, 1,
'Sales Buckets'[Bucket] = ">10M" && NetSales > 10000000, 1,
'Sales Buckets'[Bucket] = "<=10M" && NetSales <= 10000000, 1,
0
)
) > 0,
1,
0
)

Step 4 – Applied as filter

I apply:

Selected Bucket = 1

and both sums and KPIs work correctly.

3️⃣ Common Sample (NOT WORKING)

This is the main issue.

🎯 Goal

I want a slicer that lets the user define a set of years (Sample Years).

Then:

👉 A company belongs to the Common Sample if:

It has non-zero and non-blank
- Net Sales
- EBITDA
- Total Assets
in ALL selected Sample Years

✔ Example

If user selects:

👉 Sample Years = {2022, 2023}

Then:

Company A → ❌ excluded (has 0 in 2023)
Company B → ✔ included
Company C → ❌ excluded (missing 2021 irrelevant, but 2022 ok → depends only on selected years)
Company D → ✔ included
Company E → ❌ excluded (no data in selected years)

🔴 Important Requirement

Once a company is included in the Common Sample:

👉 We must be able to analyze it across ALL years (e.g. 2020–2025)

—not only the selected sample years.

❗ Problem

I attempted multiple approaches using:

measures (COUNT / FILTER / SUMX)
disconnected tables (Sample Years)
TREATAS
APPLY FILTER logic similar to Sales Buckets

However:

Results either return all companies
or all BLANK values
or do not respond to Sample Year slicer
or create circular dependency errors when trying calculated tables

❓ QUESTION

What is the correct modeling approach in Power BI to implement:

👉 A dynamic common sample filter (based on multiple selected years)

that:

Filters companies based on validity across selected years
Works together with other filters (Year, Region, Sector, Sales Bucket)
Still allows analysis across all years
Works correctly with aggregated measures and weighted KPIs

Any guidance on proper DAX pattern or data modeling approach would be greatly appreciated! 😄

Re: Power BI Modeling Question – Dynamic Common Sample + Buckets + Weighted KPIs

Juan-Power-bi — Fri, 24 Apr 2026 19:56:41 GMT

I hope it helps

Great problem description. The pattern you need is the same "virtual filter" approach as your Sales Buckets — a disconnected Sample Years table driving a measure that identifies valid companies, which you then apply as a visual-level filter.

Step 1 Disconnected Sample Years table
daxSample Years = DATATABLE("SampleYear", INTEGER, {{2020},{2021},{2022},{2023},{2024},{2025}})
Use this as your slicer (not the main date table).
Step 2 How many sample years are selected
daxSelected Sample Years Count =
COUNTROWS(VALUES('Sample Years'[SampleYear]))
Step 3 Common Sample flag
daxIn Common Sample =
VAR SampleYears = VALUES('Sample Years'[SampleYear])
VAR RequiredYears = COUNTROWS(SampleYears)
VAR CurrentVAT = MAX('Page1'[VAT])

VAR ValidYears =
CALCULATE(
COUNTROWS(
FILTER(
SampleYears,
VAR y = 'Sample Years'[SampleYear]
VAR ns = CALCULATE([Net Sales], 'Page1'[Year] = y, 'Page1'[VAT] = CurrentVAT, ALL('Page1'[Year]))
VAR eb = CALCULATE([EBITDA], 'Page1'[Year] = y, 'Page1'[VAT] = CurrentVAT, ALL('Page1'[Year]))
VAR ta = CALCULATE([Total Assets], 'Page1'[Year] = y, 'Page1'[VAT] = CurrentVAT, ALL('Page1'[Year]))
RETURN NOT ISBLANK(ns) && ns <> 0 && NOT ISBLANK(eb) && eb <> 0 && NOT ISBLANK(ta) && ta <> 0
)
),
ALL('Page1'[Year])
)

RETURN IF(ValidYears = RequiredYears, 1, 0)
Step 4 Apply as visual-level filter
Add In Common Sample = 1 as a visual-level filter on your visuals. Since the Year slicer (your main date axis) is separate from the Sample Years slicer, you can freely analyze across all years while the common sample definition is controlled by Sample Years independently.

Re: Power BI Modeling Question – Dynamic Common Sample + Buckets + Weighted KPIs

FBergamaschi — Sat, 25 Apr 2026 11:31:43 GMT

Hi atziovara

why did you unpivot the original table?

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Re: Power BI Modeling Question – Dynamic Common Sample + Buckets + Weighted KPIs

atziovara — Mon, 27 Apr 2026 08:29:55 GMT

Juan-Power-bi Thank you very much for your reply!

I am afraid that your solution only works for one single year, for example if I select 2020, my table will return the metrics of companies who have non-zero and non-blank Net Sales, EBITDA, and Total Assets in 2020. If I select in my Sample Years[SampleYear] slicer 2 years or more, all the measures on my table turn blank. The same thing happens if I select any year in my Page1[Financial Year] slicer, therefore I cannot filter the years for which I would like my financial metrics and KPIs to be shown/computed.

Re: Power BI Modeling Question – Dynamic Common Sample + Buckets + Weighted KPIs

atziovara — Mon, 27 Apr 2026 12:51:19 GMT

FBergamaschi Thank you very much for your reply! I thought that it would be a more efficient solution, as one tutor had suggested to me that in general unpivoting tables is a good way of working with data of companies. What would you suggest?

Re: Power BI Modeling Question – Dynamic Common Sample + Buckets + Weighted KPIs

johnt75 — Mon, 27 Apr 2026 10:38:09 GMT

As others have mentioned, you will need a disconnected table to hold the years for your sample selection. That should only be used on the slicer to select the sample years. You can then create a measure like

Company is in common sample = VAR _Years = TREATAS ( VALUES ( 'Sample Years'[Year] ), 'Date'[Year] ) VAR _NumYears = COUNTROWS ( _Years ) VAR _YearsAndNumbers = CALCULATETABLE ( SUMMARIZECOLUMNS ( 'Date'[Year], "@Net sales", [Net sales], "@ebitda", [EBITDA], "@total assets", [Total assets] ), REMOVEFILTERS ( 'Date' ), _Years ) VAR _ValidYears = FILTER ( _YearsAndNumbers, [@Net sales] <> 0 && [@ebitda] <> 0 && [@total assets] <> 0 ) VAR Result = IF ( COUNTROWS ( _ValidYears ) = _NumYears, 1 ) RETURN Result

and use this as a visual level filter, set to show only when the value is 1.

I think that this should work on an individual company level, but it won't work at the total level. If you need totals then a slightly different approach will be needed.

Re: Power BI Modeling Question – Dynamic Common Sample + Buckets + Weighted KPIs

atziovara — Mon, 27 Apr 2026 14:05:55 GMT

johnt75 Thank you! I am afraid that this is not working either. Again, when I select 2 years, everything is blank.
I don't wish to compute the metrics and KPIs on an individual company level, but rather in totals.More specifically, I need a dynamic common sample filter (based on multiple selected years), that:

Filters companies based on validity across selected years
Works together with other filters (Year, Region, Sector, Sales Bucket)
Still allows analysis across all years
Works correctly with aggregated measures and weighted KPIs

I would really appreciate it if you have any other ideas, even if this means not unpivoting my data, as it is very important for me. Thank you so so much 😄

Re: Power BI Modeling Question – Dynamic Common Sample + Buckets + Weighted KPIs

johnt75 — Mon, 27 Apr 2026 14:32:35 GMT

I think you need to use a calculation group so that you can work out the list of valid companies and then use that as a filter on your chosen measure.

Create a calculation item like

Company is in common sample = VAR _Years = TREATAS ( VALUES ( 'Sample Years'[Year] ), 'Date'[Year] ) VAR _NumYears = COUNTROWS ( _Years ) VAR _CompaniesYearsAndNumbers = CALCULATETABLE ( SUMMARIZECOLUMNS ( Company[Company ID], 'Date'[Year], "@Net sales", [Net sales], "@ebitda", [EBITDA], "@total assets", [Total assets] ), REMOVEFILTERS (), _Years ) VAR _CompaniesYears = FILTER ( _CompaniesYearsAndNumbers, [@Net sales] <> 0 && [@ebitda] <> 0 && [@total assets] <> 0 ) VAR _CompaniesAndNumYears = GROUPBY ( _CompaniesYears, Company[Company ID], "@num years", SUMX ( CURRENTGROUP (), 1 ) ) VAR _ValidCompanies = SELECTCOLUMNS ( FILTER ( _CompaniesAndNumYears, [@num years] = _NumYears ), Company[Company ID] ) VAR Result = CALCULATE ( SELECTEDMEASURE (), KEEPFILTERS ( _ValidCompanies ) ) RETURN Result

and then apply that as a filter to the visuals you want to be affected by your common sample filters.

The algorithm is relatively straitforward. First you need to calculate the qualifying measures for each company for each year selected. You can then filter out those rows where the qualifying measures are not all non-zero, and count the number of remaining years for each company.

You only want the companies where the number of valid years matches the number of years selected by the user, and you can use that list of valid companies as an additional filter on top of any other existing filters to calculate the underlying measure.

Re: Power BI Modeling Question – Dynamic Common Sample + Buckets + Weighted KPIs

RDewi — Wed, 29 Apr 2026 20:29:04 GMT

Hi,

I'm attaching a test.pbix, pls check if this is what you are looking for. https://drive.google.com/file/d/141G2onZUkz8eInk4OPWa_sbB0Uc_96Iz/view?usp=drivesdk

Re: Power BI Modeling Question – Dynamic Common Sample + Buckets + Weighted KPIs

v-ssriganesh — Thu, 30 Apr 2026 05:25:44 GMT

Hello atziovara,

We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.

Thank you.

Re: Power BI Modeling Question – Dynamic Common Sample + Buckets + Weighted KPIs

v-ssriganesh — Mon, 04 May 2026 04:01:10 GMT

Hello atziovara,

Hope everything’s going great with you. Just checking, the issue been resolved or are you still running into problems? Sharing an update can really help others facing the same thing.

Thank you.