Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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)
(2)
I would really appreciate your help! Thank you very much in advance!
Solved! Go to 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.
Simple enough,
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:
@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,
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!
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:
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,
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
26 | |
20 | |
19 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
24 | |
24 | |
22 |