Forum Discussion

CK60's avatar
CK60
New Member
4 years ago

Matrix Measure showing aggregate value and does not change based on Hierarchy

Hi Everyone,

Please be kind as I am new to power bi.

These are the sample results I am getting , I only included two customers in this example: OrderQTY * AVGCS Rate = Cost

I have the detailed measure definitions below. 

I was expecting the customer costs to roll up and reflect the total plant costs .

The customer  order quantities do sum up correctly to the plant qty

The average does not , but not sure if it would being it would be average of averages.

 

NetworkN1N2N3  
    Order QTYAVG CS RateCost  
       Plant  269877$1.73$468,203.71  
                Cust 1  4226$105.79$447,051.00  
                  Cust 2  8921$50.12 $447,096.240  
      
      

 

I have created a matrix that displays the above .

QTY = sum(orderqty),

Average Rate= This is a measure that consists of summation of different case rates  by department.

then getting the average of the total case rate.

 

Example : Claims CS Rate =

DIVIDE(
CALCULATE( SUM('Dim.HR'[Total Monthly Cost]),FILTER('Dim.HR','Dim.HR'[Department] = "Claims")),
SUM('Fact.Orders'[OrderedQtyInCases]),
0
)
 
After getting CS rates, I then add them up 
CS RT A+CS RT B + CS RT C  = TotalCaseRate
AVG Case RT = TotalCaseRate/3
 
The main issue is that at the plant level , the total cost for this plant is about 470k ,
so even though the row by row calculations seem correct, these customers aggregate costs is way over  the total plant cost.
Being new, I am sure that there is something I am missing.
 There is an indirect relationship between DimHR and FactOrders.. through the DimCalendar .
Maybe the hierarchy is the issue , one customer can be supplied by multiple plants
and one plant can also have multiple networks.
Sorry this is a bit convoluted but is there anyone that can try to point me in the right direction?
 
 
 

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi CK60 ,

     

    Welcome to the community.

    Sorry, still not very clear.

    Hope to provide sample data and expected results.

    Are you referring to your Rate Cost being miscalculated in the Plant hierarchy? It should be 466887.21 instead of 468203.71.

     

     

    How to Get Your Question Answered Quickly - Microsoft Power BI Community

     

     

    Best Regards,

    Stephen Tao

     

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