Forum Discussion

atziovara's avatar
atziovara
Helper I
3 months ago
Solved

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

🔹 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 m...
"DAX " "DAX help" 'Need help' 'DAX'
"DAX Commands and Tips"
  • johnt75's avatar
    johnt75
    3 months ago

    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.

johnt75's avatar
johnt75
Super User
3 months ago

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's avatar
atziovara
Helper I
3 months ago

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's avatar
    johnt75
    Super User
    3 months ago

    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.