Stuck on matrix formatting and calculations

[labontee]

Hi,

Looking to format a single row, circled in red, and its values in a matrix and create a measure that SUMs a column based on a specific text and subtracts it from another SUM of the same column based on a different text. 

i.e. If the text is Revenue, SUM it and if the text is Expense, SUM it and then subtract Expenses SUM - Revenue SUM. I'd also like to add it a starting value so I can track balance over time (years). For example, I'd like to have the previous year's balance add to the next year's starting point and then do the  If the text is Revenue, SUM it and if the text is Expense, SUM it and then subtract Expenses SUM - Revenue SUM to provide the ending fund balance for that year. My starting year would just be a reference to a fixed amount.

[Anonymous]

Hi  @labontee ,

I created some data:

Here are the steps you can follow：

1. Create measure.

Measure =
IF(
    MAX('Table'[Type])="Revenue",SUMX('Table',[Value]),
IF(
    MAX('Table'[Type])="Expense"&&ISFILTERED('Table'[Group])=FALSE(),
    SUMX('Table',[Value])
    -
    SUMX(FILTER(ALL('Table'),'Table'[Type]="Revenue"&&'Table'[Year]=MAX('Table'[Year])),[Value])
    ,SUM('Table'[Value])))

2. Result:

It's not very clear how the "track balance over time (years)" works, could you express the expected results as a picture?

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[labontee]

Thanks for putting all of this toegether. I've included a photo of the table I have in Excel that I'd like to replicate in Power BI. Each year's beginning fund blanace is the previous year's ending fund balance. The ending fund balance is the differenece of reveneues and expenses (sources and uses below). I also need to add in a beginning fund balance to start the whole data set off for FY 2016.

[ExcelMonke]

Consider the following:

Your Measure = 
VAR _Revenue = Your Revenue Calculation
VAR _Expense = Your Expense Calculation

RETURN
IF(Table[Type]="Revenue",_Revenue,_Expense)

[labontee]

Not exaclty what I was thinking but it did lead me down a rabbit hole of understanding variables. I was able to take this and learn how to subtract expenses from revenue for a given year.