Calculation Group for the matrix Income statement

[AhmadImbaidin]

Hello everyone,

Is there a way to get the same result as it shown in the picture below using the calcualtion group ?

i used more than a 100 measure to achieve this result and it made the report too slow

if so, how should the data be structured in excel file? 

appreciate you help !

thank you

[v-huijiey-msft · Accepted solution]

Hi @AhmadImbaidin ,

Calculation groups are displayed in the reporting client as tables with a single column.

Calculation groups solve the problem of proliferating redundant measures in complex models that may use the same calculations - most commonly time-intelligent calculations. For example, a sales analyst wants to view sales and orders by month-to-date (MTD), quarter-to-date (QTD), year-to-date (YTD), prior year-to-date (PY), and so on.

Before applying the Calculation Group, the basic metric Sales is commonly used to calculate total sales for each month.

Then, Time Smart Calculations are applied to get the total sales for the month-to-date, year-to-date, and so on. If there is no calculation group, the user must select a single Time Intelligence metric.

For a Calculation Group named TimeSmart, when the user drags a Time Calculations item to the Columns filter area, each calculation item will appear as a separate column. The value of each row is calculated from the base metric Sales.

SELECTEDMEASURE function: An expression from the calculated item is used to reference the metric value currently located in the context.

You can refer to:

Current

SELECTEDMEASURE()

MTD

CALCULATE(SELECTEDMEASURE(), DATESMTD(DimDate[Date]))

YTD

CALCULATE(SELECTEDMEASURE(), DATESYTD(DimDate[Date]))

PY

CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR(DimDate[Date]))

PY MTD

CALCULATE(
    SELECTEDMEASURE(),
    SAMEPERIODLASTYEAR(DimDate[Date]),
    'Time Intelligence'[Time Calculation] = "MTD"
)

PY YTD

CALCULATE(
    SELECTEDMEASURE(),
    SAMEPERIODLASTYEAR(DimDate[Date]),
    'Time Intelligence'[Time Calculation] = "YTD"
)

For more information on this, please see:

Calculation groups in Analysis Services tabular models | Microsoft Learn

Create calculation groups in Power BI (preview) - Power BI | Microsoft Learn

If you have any other questions please feel free to contact me

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[AhmadImbaidin]

it's working perfectly ! thank you

but i still have a problem with the icone conditional formatting

when i calculate the change, it works just fine with non-expenses accounts

how do i make if it's expense then multiple it by -1 to show me red icon instead if the expense is higher than the budget or the last year ? 

[v-huijiey-msft]

Hi @AhmadImbaidin ,

Assuming you have a column that differentiates between expense and non-expense accounts, you could create a measure that identifies whether an account is an expense and, if so, multiply the change by -1.

Expense Indicator =
IF(
     [AccountType] = "Expense",
     -1 * [ChangeValue],
     [ChangeValue]
)

You can adjust the specific syntax according to your own needs.

Drag the measure you created to the report page for display, and apply icon conditional formatting to it.

In the Icon dialog box, under Format Style, select Rules. Define a rule to display a red icon for values less than 0.

For more detailed steps on applying icon conditional formatting, you can refer to the official documentation here:

Apply conditional table formatting in Power BI - Power BI | Microsoft Learn

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[AhmadImbaidin]

i don't know what am doing wrong but it's not working 

i created a new column to seperate the expenses accounts from the others but i can't use it in the if statement like you did.

Also, am having all my calculations in calculation groups you suggested before.

here is a snap of what my data table looks like