Problem with table totals

[Benato]

Hi there,

I am struggling with a problem related to the total and subtotal values in a table visual. Although my total number is correct, the subtotal values in each row are incorrect, and the subtotals of each row do not add up to my total, as can be seen in the figure below.

For my measure a use the follow DAX formula: 

TesteKPI_AderenciaPeriodicasAcu_1 =

Var StDate = MIN('Calendar'[Date])

Var EndDate = MAX('Calendar'[Date])+1

Var PrevExec =

calculate(DISTINCTCOUNT(Ordens_SPCM[OS]),Ordens_SPCM[DescSimpOS]="PREVENTIVA",Ordens_SPCM[DataConsi]>=StDate,Ordens_SPCM[DataConsi]<=EndDate,Ordens_SPCM[StatusUser]<>"15NE",Ordens_SPCM[Nivel2StSist]= "ENTE CONF",Ordens_SPCM[dtFimReal]>=StDate,Ordens_SPCM[dtFimReal]<EndDate)

Return

CALCULATE(PrevExec)

-------------------------------------------------------

During my previous analyses, I found that removing the following from the formula: "Ordens_SPCM[dtFimReal]<EndDate" helped the subtotal and total values work correctly. However, when I added it back, my numbers did not add up to the total.

Do you have any tips for how to handle this problem?

[Anonymous]

Hi @Benato ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

In addition, you can also take a look at the following blog about measure calculated with multiple aggregations if helps:

Measure Totals, The Final Word 

Regards,

Xiaoxin Sheng

[Benato]

Hi,

I uploaded the pbix file in the follow link: KPI_Man.pbix

Regards,

Benato