Measure showing wildly different values in chart than in table

DAX DAX Calculations
1

Hi All,

I have a measure that is producing the correct figures in a table, but wildly different and inconsistent results in a clustered column and line chart.

All of the filters are the same and whatever I try doesn’t fix the problem, despite weeks of sleepless nights and going round in circles!

Any help would be greatly appreciated.

Here is the measure:

Actual Utilised Days = SUMX(VALUES(BookingVehicles[VehicleId]), [Total Days Utilised])

and here is Total Days Utilised:

Total Days Utilised =

    var sameVehicle = COUNTROWS (
                        FILTER (
                            VALUES ( BookingVehicles[BookingId] ),
                                CALCULATE ( COUNT ( BookingVehicles[VehicleId] ) > DISTINCTCOUNT ( BookingVehicles[VehicleId] ) )
                                )
                                )

        return {
         IF(NOT(sameVehicle), 
            SUMX(
                VALUES(BookingVehicles[BookingId]), [New No of Utilised Days]
                ), 
            DIVIDE(SUMX(
                VALUES(BookingVehicles[BookingId]), [New No of Utilised Days]
                ), 2 )
         )
        }

And lastly, here is New No of Utilised Days:

New No of Utilised Days =

var bookingID = MAX(BookingVehicles[BookingId])
var vehicleSwap = COUNTROWS(FILTER(BookingVehicles, BookingVehicles[BookingId] = bookingID)) >1
var noVehicleSwap = COUNTROWS(FILTER(BookingVehicles, BookingVehicles[BookingId] = bookingID)) = 1
var moreThanTwoVehicles = COUNTROWS(FILTER(BookingVehicles, BookingVehicles[BookingId] = bookingID)) > 2
var endTime = IF(noVehicleSwap, MAX(BookingVehicles[Effective End Datetime]), IF(MAX(BookingVehicles[Actual Delivery Time]) <MAX( BookingVehicles[Effective End Datetime]) && MAX(HireStatus[Hire Status Name]) = "Finalised", MAX(BookingVehicles[Actual Delivery Time]), MAX(BookingVehicles[Effective End Datetime])))   
var startTime = IF(MAX(BookingDates[ChargeFromDateTime]) > MAX(BookingVehicles[Effective Start Datetime]), MAX(BookingDates[ChargeFromDateTime]), MAX(BookingVehicles[Effective Start Datetime]))
var TotalMinutesHired = DATEDIFF(startTime, endTime , MINUTE)
var totalDaysHired = DATEDIFF(startTime, endTime, DAY)
var endGracetime = MAX(BookingVehicles[Effective End Time])
var startGraceTime = MAX(BookingVehicles[Effective Start Time])
var Grace = IF(endGracetime < startGraceTime, 0, MOD(TotalMinutesHired,1440)) // Capture early returns
var GraceDiff = Grace - 1438  // To capture whole days from 00.01 to 23.59 that are not grace time
var AllocatedGraceDays = MAX(BookingDates[GracePeriod])  // From vehicle swaps etc
var diffMinutes = MAX(BookingVehicles[DeliveryTime]) < MAX(BookingVehicles[Effective Start Time])
var notLastVehicle = MAX(BookingVehicles[Effective Start Date]) = MAX(BookingDates[ChargeFromDate])
var Days =  MAX(BookingVehicles[Effective End Datetime]) - MAX(BookingVehicles[Effective Start Datetime]) - AllocatedGraceDays
var LastDayofDateRange = MAX(Dates[Date])
var FirstDayofDateRange = MIN(Dates[Date])

var result = 
    SUMX(
        FILTER(BookingVehicles, NOT(ISBLANK(BookingVehicles[VehicleId]))), 
        var firstDayOfUtilisation = startTime
        var lastDayOfUtilisation = endTime

        return 
            IF(
                ISBLANK(firstDayOfUtilisation) || firstDayOfUtilisation > lastDayOfUtilisation,
                0,
                COUNTROWS(
                    INTERSECT(
                        CALENDAR(firstDayOfUtilisation, lastDayOfUtilisation), 
                        VALUES('Dates'[Date])
                    )
                )
            )
    )

var allowBlank = NOT(ISBLANK([No of InFleet Days]))   
var isBlank = AND(allowBlank, ISBLANK(result))

return IF(isBlank, 0, IF([lastDayOfUtilisation] > LastDayofDateRange,result,
            IF([firstDayOfUtilisation] >= FirstDayofDateRange && [lastDayOfUtilisation] <= LastDayofDateRange, result - 1,
            IF(TotalMinutesHired< 1440, result,
            IF(GraceDiff = 0, result,
            IF(vehicleSwap && notLastVehicle && moreThanTwoVehicles, result +1,
            IF(vehicleSwap && notLastVehicle, result,
            IF(diffMinutes && vehicleSwap, result,                              
            IF(Grace >59, result +1, result
                
            ))))))))

)

Once again, thanks in advance for any help

2

Hi @PaulR17, I noticed you didn’t provide a PBIX file. Providing one will help users and experts find a solution to your inquiry faster and better.

A perfect initial question includes all of the following:

  • A clear explanation of the problem you are experiencing

  • A mockup of the results you want to achieve

  • Your current work-in-progress PBIX file

  • Your underlying data file (to allow us to go into Power Query if necessary to transform your data and/or data model – often DAX questions really end up being data modeling solutions)

Check out this thread on Tools and Techniques for Providing PBIX Files with Your Forum Questions

Not completing your data may sometimes cause delay in getting an answer.

3

Here is an anonymised pbix file to illustrate the issue.

The values in the main table in FIN - Historic Utilisation are correct, but the Actual Utilised Days is incorrect in the chart to the right.

The values are also mostly incorrect in the F - Weekly Historic Utilisation - Revised report.

Thanks again for any pointers on how to fix this

ForumExample.pbix (7.6 MB)

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
5

Hi @deltaselect , thank you so much for taking the time to write such a detailed reply, I really appreciate it.

I will work through your recommended steps and let you know if I find the solution.

6

Hello @PaulR17 , following up if you were able to solve your query using the solutions suggested above?

7

Unfortunately, I am not going to have time to try these solutions until next week.

I will update on my progress as soon as possible

8

Hi @PaulR17, since the post will most likely become stale in the coming days, we will tag a response on this post as “Solution". Kindly, create a separate post if the suggested steps did not provide solution. Otherwise, please update if the steps given by @deltaselect helped solve your query. Thanks!

9

Hi @deltaselect I have now managed to solve the issue by following your advice and adding and moving fields in the chart version until it gave the correct results.

Many thanks again for your help