Forum Discussion
Wrong total - DISTINCT COUNT
Hello PBI comunity,
I'm currently having an issue where my measure filters my needs correctly, but the total is wrong.
Here in the table, you can see it has only 27 lines, each line has a number 1, 1x27=27. But the total shows 31. How is this possible?
I'm putting the measure below just to show how the calculation is made, but as I said, it filters all the items correctly, the table is correct, only the total is wrong.
Is it a bug?
27 lines with a number 1 each, summarizing 31
Ok, I’ve understood the issue.
It depends on how DataConsiderada is calculated.
Take titulodeproducto = 97 as an example. For this product, DataConsiderada is 16/01/2025, but Data Emissão is 17/01/2025.
So, when producto 97 is in the filter context, it gets skipped because DataConsiderada is lower than Data Emissão.
However, at the total level, there is no filter on titulodeproducto, so DataConsiderada becomes 30/01/2025, and 32 products meet the criteria.
Hope I made my point, this is how filter context works and how dax calculate totals
So the solution to your issue really depends on what you want to achieve but, as I can see, one easy workaround could be.
Create a new calculated column with the definitive date, like you calculated DataConsiderada.
So the defdate column=
dd = IF(ISBLANK('ADP06-FR-0006 Cadastro do Produto'[Data Alterada]),IF(ISBLANK('ADP06-FR-0006 Cadastro do Produto'[Data Definida]),'ADP06-FR-0006 Cadastro do Produto'[Data Preliminar para emissão],'ADP06-FR-0006 Cadastro do Produto'[Data Definida]),'ADP06-FR-0006 Cadastro do Produto'[Data Alterada])then adjust the measure like this:
EmitidosnoPrazo =
VAR EmitidosPrazo =
CALCULATE(
COUNTROWS('ADP06-FR-0006 Cadastro do Produto'),
NOT(ISBLANK('ADP06-FR-0006 Cadastro do Produto'[Data Emissão])),
'ADP06-FR-0006 Cadastro do Produto'[Data Emissão] <= 'ADP06-FR-0006 Cadastro do Produto'[dd],
'ADP06-FR-0006 Cadastro do Produto'[Descrição Revisão] <> "Preliminar"
)
RETURN
EmitidosPrazo
4 Replies
Looks like you have blank rows?
Could you provide sample data?I'm attaching a link to a GoogleDrive with a sample file.
Please note that the measure filters correctly but the total display is wrong.
Filter it by the month 1 and you'll see the question posted above, 27 lines to a 31 total. But all the n° lines x totals are wrong.
https://drive.google.com/file/d/1uElaZg9IkiQGdnz2cS0-jWR5SEaHRiEC/view?usp=sharingOk, I’ve understood the issue.
It depends on how DataConsiderada is calculated.
Take titulodeproducto = 97 as an example. For this product, DataConsiderada is 16/01/2025, but Data Emissão is 17/01/2025.
So, when producto 97 is in the filter context, it gets skipped because DataConsiderada is lower than Data Emissão.
However, at the total level, there is no filter on titulodeproducto, so DataConsiderada becomes 30/01/2025, and 32 products meet the criteria.
Hope I made my point, this is how filter context works and how dax calculate totals
So the solution to your issue really depends on what you want to achieve but, as I can see, one easy workaround could be.
Create a new calculated column with the definitive date, like you calculated DataConsiderada.
So the defdate column=
dd = IF(ISBLANK('ADP06-FR-0006 Cadastro do Produto'[Data Alterada]),IF(ISBLANK('ADP06-FR-0006 Cadastro do Produto'[Data Definida]),'ADP06-FR-0006 Cadastro do Produto'[Data Preliminar para emissão],'ADP06-FR-0006 Cadastro do Produto'[Data Definida]),'ADP06-FR-0006 Cadastro do Produto'[Data Alterada])then adjust the measure like this:
EmitidosnoPrazo =
VAR EmitidosPrazo =
CALCULATE(
COUNTROWS('ADP06-FR-0006 Cadastro do Produto'),
NOT(ISBLANK('ADP06-FR-0006 Cadastro do Produto'[Data Emissão])),
'ADP06-FR-0006 Cadastro do Produto'[Data Emissão] <= 'ADP06-FR-0006 Cadastro do Produto'[dd],
'ADP06-FR-0006 Cadastro do Produto'[Descrição Revisão] <> "Preliminar"
)
RETURN
EmitidosPrazo
