Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
atziovara
Helper I
Helper I

Distinct count to match VATs and Years

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 NumberFinancial YearNet Sales
80022018100000
80032018200000
80042018null
80022019400000
80032019500000
80042019600000
800220200
80032020800000
80042020900000
800220211000000
800320211100000
800420211200000
800220221300000
800320221400000
800420221500000
800220230
800320231700000
800420231800000
800520231900000


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!

2 ACCEPTED SOLUTIONS

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.

View solution in original post

ThxAlot
Super User
Super User

Simple enough,

ThxAlot_0-1732914923063.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



View solution in original post

9 REPLIES 9
ThxAlot
Super User
Super User

Simple enough,

ThxAlot_0-1732914923063.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



@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
Helper I
Helper I

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

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
Super User
Super User

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

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

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.

@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
Super User
Super User

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Top Solution Authors