PowerBI, visual total not matching individual row total

user_19490309 0
Viewed 143

I am new to PowerBI. I have a couple of questions.

  1. The primary question relates to visual totals. I am aware why visual totals would not match individual row sum (measure calculation is applied to the context of the total). I am trying to figure out how to resolve this mismatch.

enter image description here

You would see that "Value" total does not match individual row sum. Below are the measures / calculated columns used:

Value = ([Balance] + sumx(Txns,[Quantity] * max(Securities[LatestValuation]))) * max(Ownership[Ownership_%])

Quantity = Sum(Txns[Qty])

LatestValuation (Calculated Column) = 
VAR LatestDate = CALCULATE(max(Pricing[ValuationDate]))
RETURN CALCULATE(MAX(Pricing[ValuePerUnit]),Pricing[ValuationDate]=LatestDate)

Here are the relationships to help understand table structure better:

enter image description here

  1. While I am using LatestValuation as a calculated column, would it be possible to use it as a measure to extract the latest value under the current context from the Pricing table? I have tried doing it, but haven't been able to fetch the correct values.

Thanks!

dax powerbi
1 Answers

It would be nice to have a sample data table/tables. for now you can test the following. Please, let me know if it helps.

sumx(
     Txns
     ,SumX(
          Values(Pricing[InvestmentName])
          ,[Quantity] * CALCULATE(max(Securities[LatestValuation]))
     )
)
user_190423790
Related