Calculation Group

[Luca2020]

Hi, 

I'm trying to use calculation groups and tabular editor for the first time but i've some problems: 

I made various measures that calculate different important values from the balance sheet, for example, all the accounts under the category "D - COSTI FISSI", if summed, give the total amount of fixed costs in a period, for example:

COSTI FISSI =

CALCULATE(

    [IMP_CE],

    'R CE MARGINE CONTRIB'[LV2 Margine Contrib]

        IN { "D - COSTI FISSI" }

)

Where IMP_CE is a measure which tells the amount, directly taken from our journal entries.

LV2 Margine is a column in a table that regroup all account into various categories: 

Account                                     LV2                            LV1

01 Trading Revenues                A - Revenues             1. Value of Production

02 Sales Revenues                    A - Other Revenues   1. Value of Production

...                                               ...                                 ...

753  Rental Lease Payments    D- Fixed Costs            2. Costs

460 Salaries                             D- Fixed Costs            2. Costs

500 Insurance                          D - Fixed Costs           2. Costs

In addition to that I also have a better balance sheet where for instance, I split our revenues according to the order completion percentage ... (an invoice can only be registered in the month in which it is issued, but I prefer to divide revenues and costs according to the real period they incurred). 

So I have to create two measure each times, one which takes the amount from the IMP_CE, and another wich takes it from IMP_TRUE. 

In addition to that, there are other measure like ROE = Income / Equity based on the previous measures. 

What i'd like to obtain, is the ability to switch in the ROE measure, the Income measure from IMP_CE and the Income True measure from the Imp_True. So if I select The first, the ROE is calculated using IMP_CE values, otherwise, using IMP_TRUE. 

I'would be even better if i can use only one measure to generate them all: 

CALCULATE(

    [IMP_CE],

    condition })

or 

CALCULATE(

    [IMP_TRUE],

    condition })

Thanks!

[technolog]

Calculation groups in Tabular Editor can be a great way to achieve this.

First, you'll need to create a calculation group using Tabular Editor. Let's call this calculation group "Measure Switcher".

Next, add a column to this calculation group, let's call it "Measure Selection". This column will have two values: "Use IMP_CE" and "Use IMP_TRUE".

Now, for the magic part. In the calculation items of the calculation group, you'll define the logic to switch between the two measures based on the selection.

For the "Use IMP_CE" calculation item, the expression would be something like:

SWITCH(
SELECTEDVALUE('Measure Switcher'[Measure Selection]),
"Use IMP_CE", [IMP_CE],
"Use IMP_TRUE", [IMP_TRUE],
[IMP_CE] // default value
)
This logic checks the selected value in the "Measure Switcher" table and returns either IMP_CE or IMP_TRUE based on the selection.

Now, when you want to calculate ROE or any other measure, you can use this logic:

ROE =
DIVIDE(
SWITCH(
SELECTEDVALUE('Measure Switcher'[Measure Selection]),
"Use IMP_CE", [Income from IMP_CE],
"Use IMP_TRUE", [Income from IMP_TRUE],
[Income from IMP_CE] // default value
),
[Equity]
)
With this setup, you can use a slicer in Power BI based on the "Measure Selection" column from the "Measure Switcher" table. When you select "Use IMP_CE", all your measures will use the IMP_CE values, and when you select "Use IMP_TRUE", they'll use the IMP_TRUE values.