Forum Discussion

AhmadImbaidin's avatar
AhmadImbaidin
Frequent Visitor
2 years ago
Solved

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 repo...
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
AhmadImbaidin
Frequent Visitor
2 years ago

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 ? 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    AhmadImbaidin
    Frequent Visitor
    2 years ago

    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