Measure showing wildly different values in chart than in table

DAX DAX Calculations
4

Hi @PaulR17 ,

I am not sure if could help you with this, the information you provide, is immense, I could provide some generic (testing) advice and some experience in those.

I should work with the following testing steps :

  1. Starting testing
  • What is exactly the SOLL (correct table figures) and the IST (incorrect graph)
  • Did the measure “Actual Utilised Days” ever work in the graph ?
  • Is the data changed ?
  • Is the data model with its relationships changed?
  1. Check granularity requirement of the measure “Actual Utilised Days”
  • Compare both reports in table-form, the SOLL has presumably more dimensions as the IST, are there differences between the two in table-forms ?
  • If Yes, add dimension(s) in IST table till result become the same, at which dimension will the result equals ?
  • Generally spoken measures require context cq dimensions to give results; the required level of detail cq number of dimensions needed for correct numbers depends how the measures are defined.
  1. Testing the required graph data with additional measures, for summarizing and/or for input.
  • Are all “Var” statements tested individually (measure outcome, instead of “Result”) in a table with the same dimension(s) as in the IST Graph ?
  • If turning off a “Var”-statement, does it provide a different outcome in the correct SOLL table, do you need all the variables for the desired outcome, can some being eliminated.
  • Assuming that the SOLL table has a higher granularity then the IST graph, summarizing data with the measure seems an issue. A lot of “If” statements are used in the measure "New No of Utilised Days " Some measures requires a measure on top, which calculates the correct totals, especially when the measure is based upon “IF”, see also the appendix below how to do that. Adding a summarize measure should be the first step to test.
  • Also a lot of “Var” statements are used. It is very well possible, as known from experience, that “Var”-input calculations do not work, and the same calculations, imported via a separate input measure do work. It is therefore advisable to replace the most likely "Var"s to (input) measures, however Max functions normally should work in Var statements.
  • It could be possible that some of the “Var” calculations do work within a detailed table with a lot of dimensions, but do not work on a higher aggregated level as they are missing context for the calculations, this should be carefully checked (see also individual Var measure testing above).
  • Generic question: is it possible to diminish the variables needed for the calculation ?
  • Testing the required IST graph data with additional measures, a summarizing measure and measure branching them, instead of using “Var” within a measure will hopefully give you correct figures.

I hope I have given you some thoughts about solving this problem.

Appendix :

  • Summarizing incorrect totals

  • Fix Incorrect Totals

  • Another example of summarizing data with DAX code:

    Total Receivables = 
    SUMX(
        ADDCOLUMNS(
            SUMMARIZE( Invoices, Invoices[Customer #], Invoices[Order ID],  Invoices[Due Date (Sams Invoice Date)], Invoices[Payment adj date eq. Sams Due Date]),
                 "Total Aging", [S Receivables Per Group]),
        [Total Aging])
1 Like