Distinct count to match VATs and Years

[atziovara]

Hi everyone!

I have a file with companies' VAT Numbers, the Financial Year, and their Net Sales during that year. It looks like this:

VAT Number	Financial Year	Net Sales
8002	2018	100000
8003	2018	200000
8004	2018	null
8002	2019	400000
8003	2019	500000
8004	2019	600000
8002	2020	0
8003	2020	800000
8004	2020	900000
8002	2021	1000000
8003	2021	1100000
8004	2021	1200000
8002	2022	1300000
8003	2022	1400000
8004	2022	1500000
8002	2023	0
8003	2023	1700000
8004	2023	1800000
8005	2023	1900000

What I would like to do is create a measure that sums the net sales for the following companies:
(1) Companies whose VAT Number appears in the data as many times as the financial years available (in this case 6 times, but it needs to be dynamic). 
-> Therefore the company with VAT 8005 should be excluded.
(2) Companies whose Net Sales are neither null nor 0 in 2023.
-> Therefore, the company with VAT 8002 should be excluded.

The issue that I am facing is that my formula only filters out the companies whose Net Sales are null or 0 during 2023, but IT DOES NOT FILTER OUT COMPANIES WHOSE VAT APPEARS ONLY FEW TIMES (like 8005 in this example). The Distinct Count is not working. There are two DAX measures that I am using but none is working:

(1)

Net Sales (Valid Companies 2023) =

VAR DistinctYearsCount = COUNTROWS(DISTINCT('Page1'[Financial Year]))  -- Count of distinct financial years

VAR ValidCompanies =

    FILTER(

        VALUES('Page1'[VAT Number]),  -- Iterate through each VAT Number

        -- Ensure this VAT appears in as many rows as there are distinct years

        CALCULATE(COUNTROWS('Page1')) = DistinctYearsCount

        &&

        -- Ensure no NULL or 0 Net Sales in 2023

        CALCULATE(

            COUNTROWS(

                FILTER(

                    'Page1',

                    ISBLANK('Page1'[Net Sales]) || 'Page1'[Net Sales] = 0

                )

            ),

            'Page1'[Financial Year] = 2023

        ) = 0

    )

RETURN

    SUMX(

        ValidCompanies,

        CALCULATE(SUM('Page1'[Net Sales]))

    )

(2)

Net Sales (Valid Companies) =

VAR DistinctYearsCount = COUNTROWS(DISTINCT('Page1'[Financial Year]))  -- Total distinct years

VAR ValidCompanies =

    FILTER(

        VALUES('Page1'[VAT Number]),  -- Iterate through each VAT Number

        -- Check if this VAT appears in all distinct financial years

        CALCULATE(DISTINCTCOUNT('Page1'[Financial Year])) = DistinctYearsCount

        &&

        -- Ensure no null or zero Net Sales in 2023

        CALCULATE(

            COUNTROWS(

                FILTER(

                    'Page1',

                    ISBLANK('Page1'[Net Sales]) || 'Page1'[Net Sales] = 0

                )

            ),

            'Page1'[Financial Year] = 2023

        ) = 0

    )

RETURN

    -- Aggregate Net Sales for valid companies

    SUMX(

        ValidCompanies,

        CALCULATE(SUM('Page1'[Net Sales]))

    )

I would really appreciate your help! Thank you very much in advance!

[johnt75 · Accepted solution]

The code isn't placing a filter on the financial year column when it is performing the sum, although it does need a tweak to remove the filters when doing the count

Net Sales ( valid companies 2023 ) =
VAR NumFinancialYears =
    CALCULATE (
        COUNTROWS ( DISTINCT ( 'Table'[Financial Year] ) ),
        REMOVEFILTERS ()
    )
VAR CompaniesAndData =
    ADDCOLUMNS (
        DISTINCT ( 'Table'[VAT Number] ),
        "@num years",
            CALCULATE (
                COUNTROWS ( DISTINCT ( 'Table'[Financial Year] ) ),
                ALLEXCEPT ( 'Table', 'Table'[VAT Number] )
            ),
        "@2023", CALCULATE ( SUM ( 'Table'[Net Sales] ), 'Table'[Financial Year] = 2023 )
    )
VAR ValidCompanies =
    FILTER ( CompaniesAndData, [@num years] = NumFinancialYears && [@2023] <> 0 )
VAR Result =
    CALCULATE ( SUM ( 'Table'[Net Sales] ), KEEPFILTERS ( ValidCompanies ) )
RETURN
    Result

On the chart visual are you using the Financial Year column from the base table or do you have a date table? If you're using the base table then that could cause issues with auto exist.

[ThxAlot · Accepted solution]

Simple enough,

[atziovara]

@ThxAlot I had to make a simple change to your formula so that it works in a graph in a way that it shows the evolution of the net sales of valid companies throughout the years. It now is working perfectly, so I accepted your answer too as a solution. Thanks a lot for your help! 

For reference, the DAX formula that is working is:

Net Sales (Valid Companies 2023) =

VAR __yr =

    COUNTROWS( ALLSELECTED( 'Page1'[Financial Year] ) )

VAR __vat =

    CALCULATETABLE(

        FILTER(

            VALUES( 'Page1'[VAT Number] ),

            CALCULATE( DISTINCTCOUNT( 'Page1'[Financial Year] ) ) = __yr

        ),

        NOT ( 'Page1'[Financial Year], COALESCE( 'Page1'[Net Sales], 0 ) ) IN { ( 2023, 0 ) }

    )

RETURN

    CALCULATE( SUM( 'Page1'[Net Sales] ), __vat )

[atziovara]

@DataNinja777 
Thank you for your response, but I am afraid you did not take into consideration the issues that I am facing.

You suggested a solution related to the second condition, which was already working fine, and unfortunately your formula is showing an error on that part. The error is that: "A single value for column 'Net Sales' in table 'Page1' cannot be determined."

As stated in my post, the formula needs to filter out the companies 8005 (based on the first condition) and 8002 (based on the second condition). My formula does filter out effectively companies that do not fit the second condition, however it does not filter out companies who do not fit the first condition. This part of the formula is the one that needs to change.

[DataNinja777]

Hi @atziovara ,

Thank you for clarifying the issue. The primary challenge lies in properly filtering out VAT Numbers that do not appear in all the distinct financial years in the dataset (i.e., ensuring the formula handles the first condition effectively). The focus will now be on resolving this issue while preserving the functionality for the second condition.

Here’s a revised approach to address both conditions:

Net Sales (Valid Companies 2023) =
VAR DistinctYearsCount = COUNTROWS(VALUES('Page1'[Financial Year]))  -- Count total distinct financial years
VAR ValidCompanies =
    FILTER(
        VALUES('Page1'[VAT Number]),  -- Iterate through each VAT Number
        -- Ensure the VAT Number appears in all distinct financial years
        CALCULATE(DISTINCTCOUNT('Page1'[Financial Year])) = DistinctYearsCount
        &&
        -- Ensure Net Sales in 2023 is not null or 0
        CALCULATE(
            SUMX(
                FILTER('Page1', 'Page1'[Financial Year] = 2023),
                'Page1'[Net Sales]
            ) > 0
        )
    )
RETURN
    SUMX(
        ValidCompanies,
        CALCULATE(SUM('Page1'[Net Sales]))
    )

The measure begins by calculating the total number of financial years in the dataset using the DistinctYearsCount variable. This count is essential to ensure that each VAT Number appears in all financial years available in the dataset. The ValidCompanies variable then filters VAT Numbers to include only those that meet two conditions: they must appear in all financial years (first condition), and they must have non-zero and non-null Net Sales in 2023 (second condition).

To handle the second condition without ambiguity, the measure uses SUMX to sum the Net Sales for 2023, ensuring that only VAT Numbers with a valid total Net Sales for that year are included. Finally, the SUMX function aggregates the total Net Sales for the filtered companies, providing the desired result.

Best regards,

[johnt75]

Try

Net Sales ( valid companies 2023 ) =
VAR NumFinancialYears =
    COUNTROWS ( DISTINCT ( 'Table'[Financial Year] ) )
VAR CompaniesAndData =
    ADDCOLUMNS (
        DISTINCT ( 'Table'[VAT Number] ),
        "@num years",
            CALCULATE (
                COUNTROWS ( DISTINCT ( 'Table'[Financial Year] ) ),
                ALLEXCEPT ( 'Table', 'Table'[VAT Number] )
            ),
        "@2023", CALCULATE ( SUM ( 'Table'[Net Sales] ), 'Table'[Financial Year] = 2023 )
    )
VAR ValidCompanies =
    FILTER ( CompaniesAndData, [@num years] = NumFinancialYears && [@2023] <> 0 )
VAR Result =
    CALCULATE ( SUM ( 'Table'[Net Sales] ), KEEPFILTERS ( ValidCompanies ) )
RETURN
    Result

[atziovara]

@johnt75 Thank you very much for your response!

I probably failed to clarify that I do not need the sum of the net sales of the valid companies solely for 2023. I am using this measure in a line graph along with the financial years, so that I can track the evolution of the net sales of these companies throughout the years. I am afraid that your formula is only producing results for 2023, whereas I need the total sum (so that it can then be broken down to each year).  

[johnt75 · Accepted solution]

The code isn't placing a filter on the financial year column when it is performing the sum, although it does need a tweak to remove the filters when doing the count

Net Sales ( valid companies 2023 ) =
VAR NumFinancialYears =
    CALCULATE (
        COUNTROWS ( DISTINCT ( 'Table'[Financial Year] ) ),
        REMOVEFILTERS ()
    )
VAR CompaniesAndData =
    ADDCOLUMNS (
        DISTINCT ( 'Table'[VAT Number] ),
        "@num years",
            CALCULATE (
                COUNTROWS ( DISTINCT ( 'Table'[Financial Year] ) ),
                ALLEXCEPT ( 'Table', 'Table'[VAT Number] )
            ),
        "@2023", CALCULATE ( SUM ( 'Table'[Net Sales] ), 'Table'[Financial Year] = 2023 )
    )
VAR ValidCompanies =
    FILTER ( CompaniesAndData, [@num years] = NumFinancialYears && [@2023] <> 0 )
VAR Result =
    CALCULATE ( SUM ( 'Table'[Net Sales] ), KEEPFILTERS ( ValidCompanies ) )
RETURN
    Result

On the chart visual are you using the Financial Year column from the base table or do you have a date table? If you're using the base table then that could cause issues with auto exist.

[atziovara]

@johnt75 I was indeed using the Financial Year column from the base table, however with the new formula you sent this is not an issue anymore. I accepted your answer as a solution. Thank you very very much!

[DataNinja777]

@atziovara ,

The issue in your DAX formulas stems from the fact that the FILTER function works on row context and doesn't evaluate the CALCULATE statements as intended for the entire table of VAT numbers. To fix this, we need to ensure that:

1. Condition 1: The VAT Number appears in all distinct financial years available in the dataset.
2. Condition 2: The Net Sales for the VAT Number in 2023 is neither null nor 0.

Here’s how you can revise your measure to address both conditions:

Net Sales (Valid Companies 2023) =
VAR DistinctYearsCount = COUNTROWS(VALUES('Page1'[Financial Year]))  -- Count of distinct financial years
VAR ValidCompanies =
    FILTER(
        VALUES('Page1'[VAT Number]),  -- Iterate through each VAT Number
        -- Check if the VAT appears in all distinct years
        CALCULATE(DISTINCTCOUNT('Page1'[Financial Year])) = DistinctYearsCount
        &&
        -- Check if Net Sales in 2023 is not null or 0
        CALCULATE(
            NOT(ISBLANK('Page1'[Net Sales])) && 'Page1'[Net Sales] <> 0,
            'Page1'[Financial Year] = 2023
        )
    )
RETURN
    SUMX(
        ValidCompanies,
        CALCULATE(SUM('Page1'[Net Sales]))
    )

The improvements to the measure focus on three main areas. First, the measure ensures that all financial years are accounted for by using DISTINCTCOUNT to count the unique financial years for each VAT Number and checking that it matches the total number of distinct financial years in the dataset.

Second, it filters out companies with invalid values in 2023 by applying the condition NOT(ISBLANK('Page1'[Net Sales])) && 'Page1'[Net Sales] <> 0, ensuring only non-zero and non-null values are considered.

Lastly, the measure dynamically adapts to the dataset's filter context, making the conditions for distinct years and valid sales in 2023 flexible and context-aware.

The expected behavior is that the measure includes VAT Numbers 8003 and 8004 because they appear in all financial years and have valid sales in 2023. Conversely, it excludes VAT Number 8002 due to having sales of 0 in 2023, and VAT Number 8005 because it does not appear in all financial years.

If further adjustments are needed, let me know.

Best regards,