Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Wrong Grand total Using HASONEVALUE

I am trying to get the correct grand total of work orders cost. So I am calculating and filtering in the variable and then trying to get the correct grand total. but it didnt work. 

 

 

 

Werkorderskosten excl OH =
VAR cost =
    CALCULATE (
        SUMX ( Jobs; Jobs[Totale werkelijke kosten] );
        FILTER ( Jobs; ISBLANK ( Jobs[Job Activa] ) );
        FILTER ( RELATEDTABLE ( Dim_Equipment ); [Aantal Vloots excl. OH] );
        FILTER (
            'Dim_Job Soort';
            NOT ( 'Dim_Job Soort'[Jobsoort]
                IN {
                "Banden, velgen, uitlijnen, sneeuwkettingen";
                "Boordcomputer inbouw/uitbouw/rep.";
                "Cleaning interieur";
                "Schade";
                "Importkeuring";
                "Slang wissel";
                "Verkoopklaar maken";
                "Werkplaats uren"
            } )
        )
    )
RETURN
    IF (
        HASONEVALUE ( Dim_Equipment[Merk] );
        ( IF ( NOT ISBLANK ( [Kilometers] ); cost ) );
        SUMX ( VALUES ( Dim_Equipment[Merk] ); cost )
    )

 

 

I am getting the belowe result.

 

The relationship betwwen jobs and dim_equipment is many to one.

 

Can anyone help?

 

Regards,

Ezz

  • Anonymous , try like this add all row/group by you are using in Matrix. I added only Table[Const Year]

     

    sumx(summarize(Table,Table[Const Year], "_1",[Werkorderskosten excl OH]),[_1])

2 Replies

  • Anonymous , try like this add all row/group by you are using in Matrix. I added only Table[Const Year]

     

    sumx(summarize(Table,Table[Const Year], "_1",[Werkorderskosten excl OH]),[_1])

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support

    Hi , Anonymous 

    Maybe  something wrong with the third  part of your formular.

    Could you please share your PBIX/some sample data and final result to me to further research  if you don't have any Confidential Information?

     

    Best Regards,
    Community Support Team _ Eason