Forum Discussion

dtgold81's avatar
dtgold81
Regular Visitor
2 years ago
Solved

Measure ignoring zero values

Hi all,

 

I've got a weird problem with a measure in a matrix. In essence, I want to calculate the change in 'Total Entries' between two fortnights.

 

I have a measure which calculates the total entries:

Total Entries = CALCULATE (DISTINCTCOUNT ( 'Chronicle Items'[Entry_Student_ID] ))+0
 
And another measure which calculates the differences between the 'Total Entries' from the earliest fortnight to the latest fortnight:
Difference Between Earliest and Latest Totals (Chronicle Items) =
    CALCULATE (
        [Total Entries],
        FILTER ( 'Chronicle Items', 'Chronicle Items'[Fortnight commencing] = MAX ('Chronicle Items'[Fortnight commencing] ) ))
    - CALCULATE (
        [Total Entries],
        FILTER ( 'Chronicle Items', 'Chronicle Items'[Fortnight commencing] = MIN ('Chronicle Items'[Fortnight commencing] ) ))
    +0
 
This works perfectly UNLESS one of the 'Total Entries' values is zero. See matrix below for example (green shows expected behaviour, red shows the problem - in the highlighted example, it should show '1' in the total change column (far right).
 
 
I've tried setting both measures to 'Decimal' number type, but that didn't help. I'm all out of ideas.
 
Any ideas what might be causing this?
 
Thanks for your help!
Need Help
  • Daniel29195's avatar
    Daniel29195
    2 years ago

    dtgold81 

    i think that problem with your code is that example the line you highlighted in red, 

    im assunmming that there is no row for this line item in 26/02.2023, 

    so in the total, 

    your code will return for max and min  --> 12/03/2023 which is 1 - 1 = 0..  and that is why you are getting such results .

     

     

     

     

     

    If my response has successfully addressed your issue kindly consider marking it as the accepted solution! This will help others find it quickly. Dont forget to hit that  thumbs up button 🫡👍

2 Replies