Share
Report
1🔹 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)
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
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!
All responses (
Answers (
