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

[atziovara]

🔹 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:

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

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

[v-ssriganesh]

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.

[v-ssriganesh]

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.

[RDewi]

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

[johnt75]

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.

[atziovara]

@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:

1. Filters companies based on validity across selected years
2. Works together with other filters (Year, Region, Sector, Sales Bucket)
3. Still allows analysis across all years
4. 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 😄

[johnt75]

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.

[FBergamaschi]

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

[atziovara]

@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?

[Juan-Power-bi]

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.

[atziovara]

@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.