Dax Parent Child Measure
I need help with a measure. I have a multiple parent hierarchy. Thus far PowerPivot only shows me the fully consolidated value. What I would like to see is the consolidated value for a particular level (meaning, all children and the entity itself), and not the value that remains at the top of the hierarchy. To illustrate my problem I have the following hierarchy: C is the child of A (50%) and B (50%). D is the child of C (100%). D has an income of 100€ and C has an income of 50€. So I have a table for my hierarchy (Nodes), one for the income (Fact Data) and one with the unique names (UniqueNames) I loaded them into the datamodel and connected them as followed: Then I added some columns: First I used the ‘Path’ function, ‘pathlength’ and then I used a lookup-function to create the Level Columns. With these levels I created a hierarchy in the data model: Then I added 4 measures: Total_Fact_Income:=SUM(Fact_Data[Income]) Total_Income:=CALCULATE([Total_Fact_Income];Nodes) PctOwnership:=SUM(Nodes[OwnershipPct]) Consolidated:=SUMX(Nodes;[Total_Income]*[PctOwnership]) When I insert a Pivot table and use the hierarchy for my row and the ‘Consolidated’ measure for my value I get: When fully expanded it shows me 25 for C and 50 for D, when collapsed to C or A it shows 75. So this gives me the consolidated values: When you follow the hierarchy path of A, 75€ will remain when the money is at A. So no matter at which level I am, Excel always show the value that remains when the income is fully consolidated (meaning arrived its end point A or B). I would like to have a measure that does not show me the value for the consolidated income all the way up (here A) , but just to the certain entity (here C). Meaning, in my model so far the consolidated income is 75; I would like to see 150 at level C (own income 50 + income from child 100). Only at the level A I would like to see 75 (150*0,5). This means that you only see the unconsolidated values when fully expanded, When collapsed to C you see the consolidated value to this level (which is simply 100 + 50) and when fully collapsed to A there you see the consolidated value of 75. Can someone help me with this measure?
Incorrect result with simple operation – Consolidated Curr. Conv. with Calculation Groups
Initial Situation: A client has different companies and each one has a main currency. Revenues can be made in different currencies by each companies. One Revenue/Sale of one company is stored in two versions. One where the revenue is stored as the Company Currency and one where the revenue is stored as euro. The column RevenueVariant indicates which is which. Goal: The client wants a report where she/he needs to filter just one dimension (Reporting Currency) and can analyze all companies in this currency. The requirements are that the revenue with the RevenueVariant=“Company Currency” should not be converted if the selected reporting currency already equals the company currency. But if they differ then the revenue with the RevenueVariant = “EUR” should be used to convert the revenue to the selected reporting currency. An fx rate table is available and contains all fx rates based on EUR. So EUR->USD is 1.2 in this example. It is important to note that the fx rate with which the revenues are calculated and stored in the database (difference in “Company Currency” and “EUR” of RevenueVariant) have not to be equal to those fx rates in the fx rate table. Implementation: Because some measures in the tabular model require different currency conversion logic. I decided to use Calculation Groups and a Wrapper-Measure for my currency conversion. This way I can adjust the currency conversion on a per measure basis. Each currency conversion logic is in a separate calculation group attribute. I am using Power BI Desktop (Report Server Version January 2020) and Tabular compatibilityLevel = 1500. I have a live connection from Power BI Desktop to the tabular model. Problem: If I want to use the consolidated and converted revenue (which is displayed correctly) in another calculation, I get a very high number that is not explicable to me. Even if I just add the number 1 to the measure I get an incorrect result. All four operations (+,-,*,/) follow that behavior. Tabular Model: Source Tables: There are three tables as data source. As mentioned before regarding the fx rates: if you calculate the fx rate of the revenue of company BBB manually you will get 13/10 = 1,3 and it differs from 1,2 from the fx rates table. But this is okay, as mentioned above. The fx rates table and the fx rates of the fact table do not have to be the same. Relationships: There is only one relationship. T_CM_DIM_Rep_Cur is connected to the FX Rates table (T_CM_FA Currency) so that if a user filters on reporting currency the correct fx rate gets selected. Calculation Group Attributes: CG_Attribute_1: SUMX ( 'T_CM_CG_Fact_Table', -- If the selected reporting currency equals the company currency no currency conversion is needed -- and the Revenue with RevenueVariant "Company Currency" is used in the current row of SUMX. IF ( 'T_CM_CG_Fact_Table'[CompanyCurrency] = SELECTEDVALUE ( 'T_CM_DIM_Rep_Cur'[Currency] ) , CALCULATE ( SELECTEDMEASURE (), 'T_CM_CG_Fact_Table'[RevenueVariant] = "Company Currency" ), CALCULATE ( SELECTEDMEASURE () --If the following statement is uncommented the addition by 1 works in the [REVENUE | CG_Attribute_1 + 1] measure: --/ SWITCH ( "EUR", "EUR", 1,"XYZ", SUM ( 'T_CM_FA CURRENCY'[RATE] ), 1 ) , 'T_CM_CG_Fact_Table'[RevenueVariant] = "EUR" ) * MAX ( 'T_CM_FA Currency'[RATE] ) ) ) CG_Attribute_2: SUMX ( 'T_CM_CG_Fact_Table', -- If the selected reporting currency equals the company currency no currency conversion is needed -- and the Revenue with RevenueVariant "Company Currency" is used in the current row of SUMX. IF ( 'T_CM_CG_Fact_Table'[CompanyCurrency] = SELECTEDVALUE ( 'T_CM_DIM_Rep_Cur'[Currency] ) , CALCULATE ( SELECTEDMEASURE (), 'T_CM_CG_Fact_Table'[RevenueVariant] = "Company Currency" ), CALCULATE ( SELECTEDMEASURE () --If the following statement is commented the addition by 1 does not work in the [REVENUE | CG_Attribute_2 + 1] measure: / SWITCH ( "EUR", "EUR", 1,"XYZ", SUM ( 'T_CM_FA CURRENCY'[RATE] ), 1 ) , 'T_CM_CG_Fact_Table'[RevenueVariant] = "EUR" ) * MAX ( 'T_CM_FA Currency'[RATE] ) ) ) Measures: [REVENUE_hidden]= SUM( 'T_CM_CG_Fact_Table'[Revenue] ) [REVENUE | CG_Attribute_1]= CALCULATE( [REVENUE_hidden] , '_CalcGroup'[Attribute] = "CG_Attribute_1" ) [REVENUE | CG_Attribute_2]= CALCULATE( [REVENUE_hidden] , '_CalcGroup'[Attribute] = "CG_Attribute_2" ) [REVENUE | CG_Attribute_1 + 1]= [REVENUE | CG_Attribute_1] +1 [REVENUE | CG_Attribute_2 + 1]= [REVENUE | CG_Attribute_2] + 1 Problematic result: Questions: Q1: Does anyone know why I get this high number after the simple addition? Q2: Why does the Measure [REVENUE + 1 | CG_Attribute_2] work while the one with CG_Attribute_1 does not? The only difference is that in CG_Attribute_2 the seemingly irrelevant code: "/ SWITCH ( "EUR", "EUR", 1,"XYZ", SUM ( 'T_CM_FA CURRENCY'[RATE] ), 1 )" is added. I found this solution only by trial and error. I have to use the FX Rate Table in the Switch statement otherwise it gives the same incorrect result as CG_Attribute_1. Also the Operator SUM in the Switch statement does not matter of course. This part of the switch statement should never be executed anyway because of the static "EUR","EUR",1 at the beginning of the switch statement. Q3: When I create a report level measure in Power BI Desktop and define the measure exactly like [REVENUE | CG_Attribute_1 + 1] I get the correct result (last column in the previous screenshot). Does anyone know why? I really appreciate any input! Thank you! -David
