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.
| Network | N1 | N2 | N3 | | |
| | Order QTY | AVG CS Rate | Cost | | |
| 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?
