Forum Discussion

alecsonline's avatar
alecsonline
Icon for Helper I rankHelper I
7 years ago
Solved

Question for PowerBI Masters: wrong year totals

Dear all, really hope someone can help me with the arvery common matter of totals calculation over ye,
but applied to my "peculiar" case.

 

The problem is in the image below: looking at total of the year for the value "RMS" you can see that is not equal to the sum of each month: the total in yellow should be 9420.

 

 

Here is a pbix file with a very short version of my report: 

 

https://1drv.ms/u/s!Ar-kpjLj8CF4jGKptz_xL-vpPYQx

 

In this pbix there are 2 data sets to take into consideration:

 

1st: "RESERVATIONS", from which comes the measure "OTB". it represents the "future"

2nd: "DATAWAREHOUSE", once the day is "closed" the system creates this table made of consumptive data. This value is represented by the measure "RMS DW". It is the "past".

 

When looking at the future I have to show the "OTB" value, when looking to the past I have to show the "RMS DW" value.

 

"Future" or "Past" depends on the "reading date", i.e. I can ask to PBI: show me which was the situation on Oct 20th (reading date 20th October)...in this case for all the days after the Oct 20th it has to show me the "future" (OTB), for all the days before the 20th it has to show me the "past" (RMS DW)

 

One very kind member of this community already helped me to write the formula to have the RMS value depending on the reading date:

RMS = SUMX(SUMMARIZE(VALUES('Calendar'[Day]);[Day];"ABCD";IF([maxreadingdate]<[maxdata];[OTB];[RMS DW]));[ABCD])

 

This works perfect on a daily and monthly but not on a yearly bases!!

How to solve it?

It is really clear to me WHY the error, but definitely not how to solve it (trust me I have tried...and tried...and tried...)

The easiest way to use the attached .pbix file is to look at the "File revenue" page. 
The "maxreadingdate" and "maxdate" in this visual are shown just to demonstrate the reason of the error: simply when it reaches the "Total" line, the condition "[maxreadingdate]<[maxdata]" ia always satisfied and therefore the value shown is the total of the OTB" values...as written in the formula.

 

PLEASE NOTE: to show the error you necessarily have to include in the visual the month that includes the "Reading date"

 

Thank you very much for any help!

  • Hi all, solved the problem! I am sharing it as it coud be useful to someone else.

    This was the original formula, with correct values on daily rows, wrong on total year:

     

    RMS = SUMX(SUMMARIZE(VALUES('Calendar'[Day]);[Day];"ABCD";IF([maxreadingdate]<[maxdata];[OTB];[RMS DW]));[ABCD])

     

    This is the correct formula:

     

     

    RMS = SUMX(SUMMARIZE(VALUES('Calendar'[DATE].[Date];[DATE].[Date];"ABCD";IF([maxreadingdate]<[maxdata];[OTB];[RMS DW]));[ABCD])

     

    The error was pointing the the "day" dimension 'Calendar'[Day] instead than to the "full date" dimension 'Calendar'[DATE].[Date]

     

    Pointing to 'Calendar'[DATE].[Date] also the total of the year is correct.

     

     

3 Replies

  • Stachu's avatar
    Stachu
    Icon for Community Champion rankCommunity Champion

    the issue is coming from the IF in the RMS

    RMS =
    SUMX (
        SUMMARIZE (
            VALUES ( 'Calendar'[Day] ),
            [Day],
            "ABCD", IF ( [maxreadingdate] < [maxdate], [OTB], [RMS DW] )
        ),
        [ABCD]
    )

    when you change < to <= the September value changes to 3783, which is aligned with total of 9417
    the OTB measure uses <= so that's seems to be more consistent as well - what's the reason for using < in RMS?

    • alecsonline's avatar
      alecsonline
      Icon for Helper I rankHelper I

      Stachu thank you very much, but unfortunately seems to be not the point....I tried it but using <= rather than < simply moves the evaluation of the result of ne day...

  • Hi all, solved the problem! I am sharing it as it coud be useful to someone else.

    This was the original formula, with correct values on daily rows, wrong on total year:

     

    RMS = SUMX(SUMMARIZE(VALUES('Calendar'[Day]);[Day];"ABCD";IF([maxreadingdate]<[maxdata];[OTB];[RMS DW]));[ABCD])

     

    This is the correct formula:

     

     

    RMS = SUMX(SUMMARIZE(VALUES('Calendar'[DATE].[Date];[DATE].[Date];"ABCD";IF([maxreadingdate]<[maxdata];[OTB];[RMS DW]));[ABCD])

     

    The error was pointing the the "day" dimension 'Calendar'[Day] instead than to the "full date" dimension 'Calendar'[DATE].[Date]

     

    Pointing to 'Calendar'[DATE].[Date] also the total of the year is correct.