Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SADA
Regular Visitor

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:

Tabular ModelTabular Model

 

 

Source Tables:

source tbls.PNG

 

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:

ResultsResults

 

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

 

 

 

 

0 REPLIES 0

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.