Measure Value Incorrect in Table Visualization

[kotarosai]

I have a measure below which calculates sales this quarter, and when putting it into a table visualization, the TOTAL is accurate. However when looking at the column values itself, it is inaccurate. When exporting the data from the table visualization, the sum of the column totals to be 1890 which is way off of the correct 105. To get the Account, I am using a vlookup calculated column, and they have a many to one relationship as well. Any feedback on how this could be happening? Thanks for your support!

Latest Date Sales = CALCULATE(

MAX('Sales Result'[CAS_Bonus_Date__c]),

'Sales Result'[RecordTypeId]="0126g000000O4VdAAK")

Sales This QTR =

VAR CurrentDate = [Latest Date Sales]

RETURN

CALCULATE(

SUM('Sales Result'[Net_Sales_Units__c]),

FILTER(

ALL('Date Table'),

AND('Date Table'[Date]>=IF(MONTH(CurrentDate)<13,Date(Year(CurrentDate),(INT((Month(CurrentDate)-1)/3)+0)*3+1,1),

Date(1900,1,1)

),

'Date Table'[Date]<IF(MONTH(CurrentDate)<13,Date(Year(CurrentDate),(INT((Month(CurrentDate)-1)/3)+1)*3+1,1),

Date(1900,1,1)

)

)

)

)

Account = lookupvalue(Account[Name],Account[Account_ID_18__c],'Sales Result'[Account_ID_18__c])

[v-alq-msft]

Hi, @kotarosai 

Thanks for providing the information. The total calculates the 'CurrentDate' in the entire 'Sales Result' table. The other rows calculates 'CurrentDate' in the context filtered by 'Account'. So there should be one or more rows where 'CurrentDate' equals to the 'CurrentDate' of total. So the sum of column values will larger than the total.

You may simplify 'Sales This QTR' measure as below.

Sales This QTR =
VAR CurrentDate = [Latest Date Sales]
RETURN
    CALCULATE (
        SUM ( 'Sales Result'[Net_Sales_Units__c] ),
        FILTER (
            ALL ( 'Date Table' ),
            QUARTER('Date Table'[Date])=QUARTER(CurrentDate)
        )
    )

Actually, a measure always operates on aggregations of data under the evaluation context. Each cell has different contexts. 

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kotarosai]

Hey Allan, I appreciate your feedback and suggestion. 

Unfortunately, your formula had the same result as my previous formula after I added an AND condition to account for the year as well, please find below. The total is correct but the rows are still calculating incorrectly. I think I may understand why now with your explanation and believe it is due to the [Latest Date Sales] measure calculating for each account. How can I write that formula so that it is the same MAX for each row? 

Latest Date Sales = CALCULATE(
    MAX('Sales Result'[CAS_Bonus_Date__c]),
    'Sales Result'[RecordTypeId]="0126g000000O4VdAAK")

Sales This QTR = 
VAR CurrentDate = [Latest Date Sales]
RETURN
    CALCULATE (
        SUM ( 'Sales Result'[Net_Sales_Units__c] ),
        FILTER (
            ALL ( 'Date Table' ),
            AND(QUARTER('Date Table'[Date])=QUARTER(CurrentDate),
            YEAR('Date Table'[Date])=YEAR(CurrentDate)
            )
        )
    )

[kotarosai · Accepted solution]

I was able to figure it out. I amended the [Latest Date Sales] formula to the below in order to find the max given the criteria that we have and SUMMARIZED it by the [Account]. This was somewhat of an inverse exmaple of when the measure totals don't add up to the column rows which I referenced many examples here, but the solution was nearly the same in that we needed to summarize the results based on the row headers. I have amended the 'Sales This QTR' formula to yours as it is simpler, so I appreciate your feedback on that Allan. Thanks!

Latest Date Sales = VAR X = SUMMARIZE(ALL('Sales Result'),'Sales Result'[Account],"M",CALCULATE(MAX('Sales Result'[CAS_Bonus_Date__c]),'Sales Result'[RecordTypeId]="0126g000000O4VdAAK"))
RETURN MAXX(x,[M])

[kotarosai]

Please let me know if there are any other points that would make this easier to troubleshoot, thanks!