Forum Discussion
Calculation Group for the matrix Income statement
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
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 TeamIf 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!
4 Replies
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 TeamIf 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!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 ?
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 TeamIf 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!
