Forum Discussion

CarlsBerg999's avatar
CarlsBerg999
Helper V
4 years ago

Correct Measure for Hierarchy Total

Hi,

 

I'm having a problem with hierarchy totals. The idea is to calculate the price of the car which is Component Qty * Component Price for all components. I have three tables: 
1. Masterdata of cars

2. List of components per car 

3. List of prices per components at a specific date

The values marked as green are correct, but the filters on the grand totals are incorrect. I want to fix this with a measure, but i dont know how.

 

 

 

 

My measure is currently:

 

Consumption Cost =

VAR MovingAverageValue =

CALCULATE (
SUMX('Material Unit Costs','Material Unit Costs'[Valuation Amount])*SUMX('Material Input','Material Input'[InputQty]),
FILTER (
'Material Unit Costs',
'Material Unit Costs'[Validity Start Date]
<= MAX ( 'Production'[Completion] )
),
FILTER (
'Material Unit Costs',
'Material Unit Costs'[Validity End Date]
>= MAX ( 'Production'[Completion] )
)
)

RETURN

MovingAverageValue
 
 
How do i fix the grand totals? 
Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Two friendly suggestions.

     

    First, try to format your code properly so that it is easy to read.

    Consumption Cost =
VAR MovingAverageValue =
    CALCULATE (
        SUMX ( 'Material Unit Costs', 'Material Unit Costs'[Valuation Amount] )
            * SUMX ( 'Material Input', 'Material Input'[InputQty] ),
        FILTER (
            'Material Unit Costs',
            'Material Unit Costs'[Validity Start Date] <= MAX ( 'Production'[Completion] )
        ),
        FILTER (
            'Material Unit Costs',
            'Material Unit Costs'[Validity End Date] >= MAX ( 'Production'[Completion] )
        )
    )
RETURN
    MovingAverageValue

     Second, paste some sample data. In this case, the three tables and the names you have shown in the image is not matching with the DAX code you posted. For example, the quantity field is not even there in the sample image. Without understanding the table structure, field names, and the relationships between them, it is difficult to actually write optimized DAX code. You would not know when it will give wrong results and why. Especially when you use CALCULATE() function. It is the single, most important function in DAX, and a lot of things will go on in the background when you use CALCULATE.

     

    https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523#M607150