locked
Dax measure - error with total RRS feed

  • Question

  • Hello everyone,

    I have a probleme with this measure I created:

    IF(SUMX(RESPONSABILI,RESPONSABILI[ID])=9,CALCULATE([MG_SALDO_TOT]-[MG_TotConsumi]),CALCULATE([MG_SALDO_TOT]))

    The calculation is correct for the single rows, but not for the total.

      Valori
    Responsabile MG_GC_Valore
    10 - Maintenance 1.050.317
    11 - Marketing 960.200
    12 - HR&O 1.865.311
    9 - Warehouse 714.332
    Totale complessivo 5.338.908
       
    Correct Total 4.590.160

    Can you help me?

    thanks

    Silvia

     

     

     

     

     

     

    Friday, January 13, 2012 10:02 AM

Answers

  • Hi Silvia,

    Do you have any filters on the rows / slicers or filters?  I suspect that the all (in MG_TOT_CONSUMI) is causing the issue.

    HTH

    Paul

    www.paultebraak.wordpress.com

    • Marked as answer by Challen Fu Tuesday, January 24, 2012 5:31 AM
    Monday, January 16, 2012 8:10 PM
  • Perhaps your intention is something like this:

    SUMX(RESPONSABILI,IF(RESPONSABILI[ID]=9,[MG_SALDO_TOT]-[MG_TotConsumi],[MG_SALDO_TOT]))

    If not, please carify what you are trying to accomplish.

    • Marked as answer by Challen Fu Tuesday, January 24, 2012 5:31 AM
    Monday, January 23, 2012 4:09 PM

All replies

  • Hi Silvia,

    Have you got any filters on the data?  I think that MG_SALDO_TOT may be using the all rather than allfiltered?

    HTH

    Paul

    www.paultebraak.wordpress.com


    Monday, January 16, 2012 7:11 AM
  • Hi Paul,

    here below you can find the sintax for all the measures involved in MG_GC_VALORE:

    MG_SALDO_TOT=SUMX(IDG_BI_SCRITTURE_ALL,IDG_BI_SCRITTURE_ALL[SALDO])+sumx(DATI_CONSOLIDATI,DATI_CONSOLIDATI[Importo])+SUMX(IDG_BI_CONSUMI,IDG_BI_CONSUMI[VALORE])

    MG_TOT_CONSUMI=CALCULATE([MG_ValConsumi],ALL(GENERALCOST[GeneralCost]),ALL(RESPONSABILI[Responsabile]))

    Thanks

    Silvia

    Monday, January 16, 2012 9:08 AM
  • Hi Silvia,

    Do you have any filters on the rows / slicers or filters?  I suspect that the all (in MG_TOT_CONSUMI) is causing the issue.

    HTH

    Paul

    www.paultebraak.wordpress.com

    • Marked as answer by Challen Fu Tuesday, January 24, 2012 5:31 AM
    Monday, January 16, 2012 8:10 PM
  • Perhaps your intention is something like this:

    SUMX(RESPONSABILI,IF(RESPONSABILI[ID]=9,[MG_SALDO_TOT]-[MG_TotConsumi],[MG_SALDO_TOT]))

    If not, please carify what you are trying to accomplish.

    • Marked as answer by Challen Fu Tuesday, January 24, 2012 5:31 AM
    Monday, January 23, 2012 4:09 PM
  • Hello everyone,

    sorry for the delay...

    What I don't understand is way the total is not being able to sum all the rows over it indipendently from the formula used on the single rows.

    Silvia

     

    Wednesday, January 25, 2012 9:42 AM
  • Silvia,

    Please show a sample of your data and the exact formula that you are using.

    Note that every cell in pivot table is evaluated independently based on its own filter context. Total rows do not refer to cells above in the pivot table.

    Wednesday, January 25, 2012 2:28 PM
  • Hi,

    thanks for your interesting on my problem.

    Here below, you can find a data sample and the sintax of my formula (as on my first post):

     

    The calculation is correct for the single rows, but not for the total.

      Valori
    Responsabile MG_GC_Valore
    10 - Maintenance 1.050.317
    11 - Marketing 960.200
    12 - HR&O 1.865.311
    9 - Warehouse 714.332
    Totale complessivo 5.338.908
       
    Correct Total 4.590.160

    Really, if the Total rows do not refer to cells above in the pivot table but to the formula, the calculation is correct (the difference between the sum of the single rows and the total row is caused by the if statement). But there is a way to make the total row calculate the sum of the rows on the top of it?

     

    Thanks,

    Silvia

    MG_GC_Valore=IF(SUMX(RESPONSABILI,RESPONSABILI[ID])=9,CALCULATE([MG_SALDO_TOT]-[MG_TotConsumi]),CALCULATE([MG_SALDO_TOT]))

    Thursday, January 26, 2012 10:03 AM
  • Please show a sample of your raw data table (not the pivot table).

    Also, try to explain in your own words (not the formula) what you intend for the measure to show.

    Thursday, January 26, 2012 2:11 PM