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
Regional Formats based on a parameter
I am trying to find a way to format currencies and dates from a parameter in a report. For example I would like to pass GBP and format currency "£#,##0.00" and dates "dd/mm/yy" or pass USD and format currency "$#,##0.00" and dates "mm/dd/yy" I can change the parameter from ISO currency codes to country codes if required. Any help would be really appreciated as I'm totally stumped with it
