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 t...
Need Help
  • alecsonline's avatar
    alecsonline
    7 years ago

    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.

     

     

Stachu's avatar
Stachu
Icon for Community Champion rankCommunity Champion
7 years ago

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
7 years ago

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...