Calculation Group Help!

[Anonymous]

Hello Community members,

I am looking for help in calculation group.

Here is the current scenario:

There are two main fact tables: Budget

& Invoice.

The requirement is to create a multi-layered report where we can compare budget vs actual based on Month, Project, or the CBS Level.

I have created a simple report using calculation group for time intelligence calculation:

The current challenge is to include the feasibility of selecting the desired currency.

I was wondering if this is possible through calculation group. Please advise.

Sample file: click here

Thanks!

vir

[Whitewater100]

Hello:

This article may help although you may need Azure Analysis Services or Analysis Services 2019.

Currency conversion in Power BI reports - SQLBI

[Anonymous]

Hello @Whitewater100 ,

Thank you for your response.

I am not particularly looking for currency conversion problem. The challenge is to select a different column based on the slicer selection from two different tables.

I will try to explain the scenario with a better example:

Following are the two fact tables, with pre-existing currency values columns.

I have created two simple measures to sum one column of the currency (AUD in this case) and a third one to calculate the difference:

Following is the data model:

I have created a calculation group for time intelligence calculation:

In addition to this, I would like to include the feasibility of including the currency selection (selecting the relevant column from both the fact tables)

For example, if we select DKK from the slicer, the matrix below should show the sum of DKK columns from Budget and Invoice, and their respective difference for the selected Time Intelligence slicer (Calculation Group).

One option is to unpivot the fact tables, with Currency as one column, but due to the sheer size of the table the solution is not feasible in long run. So, I was exploring if this can be achieved using calculation group.

You may find the sample data model here

Thanks again for your time and help.

br

vir

[Whitewater100]

Hi:

I understand it more now. I beleive you will want to have your currency index as your slicer that has a one to many relationship with both your invoice and budget tables.

The other modeling idea is to structure your info a bit differently.  Both the Budget and Invoice tables can be unpivoted and include your currency index/ID key.

Roughly:

Currency ACT USD Index 1

               ACT GBP Index 2   etc  This is the lookup table or slicer which connects to the other fact tables.

The Fact tables would be more like:

Proj ID   xxxV12   Date  Amount  Curr_ID

They now will be much longer and not as wide. Both of these fact tables conect to your slicer table on the Curr_ID which will solve your problem.

I'm more used to seeing a currency conversions applied against one amount and mostly calculating out each countries amonts vs. having all the figures complete like you have. Either way you'll want a slicer table for currencies, joined on an index key in a one to many relationship with both of your fact tables. Those fact tables will have currency ID that join to the currency slicer table.

I hope this make sense! Thanks 

[Anonymous]

Hello @Whitewater100 

Thank you for your response.

I am aware about the unpivot option, but as mentioned in my last response, it will increase the size of the tables manyfold, and these are ever growing tables. So the unpivot solution may not fit as a long term solution. Hence, I was looking to solve this through Calculation Groups.

br,

vir