Custom grand total (exclude membership)

[dla005]

Hello,

Is it possible to create a custom Grand total (EBITDA) in Power Bi that exclude certain financial line item in a single matrix table?

Currently I have a PL that consist of the below financial line items and I want EBITDA to exclude membership. Currently the only way i know how to get pass this and get the correct EBITDA is to create 2 matrix table - 1 for Membership by itself and 1 for Revenue/ Cost. However, i would prefer both being on the same matrix  table if possible.

1. Membership

2. Revenue

3. Cost of goods

4. EBITDA

[dla005]

That works perfectly! Thank you so much for your help @v-lionel-msft ! I can finally finish my report lol

[v-lionel-msft]

Hi @dla005 ,

Like this?

Best regards,
Lionel Chen

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

[dla005]

Thank you @MFelix  and @v-lionel-msft for responding!

Per your sample above @v-lionel-msft it is exactly how I am set up right now, but I would like to combine both the Member and all the other line items in a single table with EBTIDA (total) not counting the member.

Please see example below, is it possible to make EBITDA 2 for budget (26-12-12) and 1 for actual (10-3-6). Thank you so much for your help!

Actua/Budget measure are the below. I do plan on adding variance and Variance % as well.

*Actual = CALCULATE(SUM(Data[Value]),FILTER(Data,Data[Budget/Actual]="Actual"))

*Actual = CALCULATE(SUM(Data[Value]),FILTER(Data,Data[Budget/Actual]="Budget"))

[v-lionel-msft]

Hi @dla005 ,

Like this?

Best regards,
Lionel Chen

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

[dla005]

Hi @v-lionel-msft ,

Thank you for taking your time to look at this.

The EBITDA result is exactly what I want but is it possible to include Members in that same table as well (14 members for Budget and 7 Members for Actuals) but for EBITDA to excude members when it is calculating. Below is the result i am looking for

	Budget	Actual
Members	14	7
Cost of Revenue	-12	-3
OPEX	-12	-6
Revenue	26	10
EBITDA	2	1

The reason is because I am building a trended income statement and if i use 2 tables for members and other financial line items then the table would not line up properly. Thank you!

[v-lionel-msft]

Hi @dla005 ,

Please refer to my .pbix file.

Best regards,
Lionel Chen

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

[dla005]

 @v-lionel-msft @MFelix    Thank you so much for helping! Both method works perfectly and my only question is it possible to create a custom table for sorting? Since it is using Filter and && I don't think I can reference to a secondary table to create a custom sorting list. My goal is to have membership 1st, Revenue 2nd, Cost 3rd, etc.

If this is not possible then I can always create a sorting list in my main data table.

Thank you both for your time!

[v-lionel-msft · Accepted solution]

Hi @dla005 ,

Please do like this.

1. Create an conditional column in 'Edit Query'.

 #"Added Conditional Column" = Table.AddColumn(#"Sorted Rows", "Custom", each if [F_L] = "Member" then 1 else if [F_L] = "Revenue" then 2 else if [F_L] = "Cost of Revenue" then 3 else if [F_L] = "OPEX" then 4 else 5),

2. Sort the [F_L] column by the [Custom] column.

At the end you will get the correct column sort.

Best regards,
Lionel Chen

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

[MFelix]

Hi @dla005 

Try the following syntax:

Actual =
VAR Actuals_calc =
    CALCULATE (
        SUM ( Data[Value] ),
        FILTER ( Data; Data[Budget/Actual] = "Actuals" )
    )
VAR actuals_Excep_members =
    CALCULATE (
        SUM ( Data[Value] ),
        FILTER (
            Data,
            Data[Budget/Actual] = "Actuals"
                && Data[Financial Line] <> "Member"
        )
    )
RETURN
    IF (
        HASONEFILTER ( Data[Financial Line] ),
        Actuals_calc,
        actuals_Excep_members
    )

you may need to adjust the column names.

[tex628]

In your actual and budget calculation try the following:

Actual =
IF( SELECTEDVALUE([Financial Line]) = BLANK(), 
CALCULATE( SUM(Amount] , [Financial Line] <> "Membership" ) ,
SUM([Amount])
)

Let me know how it goes! 🙂 

Br,
J

[MFelix]

Hi @dla005 ,

This depends on how you are maknig you calculations but if the Membership is a category in your models you need to Filter out the membership from the calculation.

Can you share a sample of your data and expected result?

Please see this post regarding How to Get Your Question Answered Quickly (courtesy of @Greg_Deckler) and How to provide sample data in the Power BI Forum (courtesy of @ImkeF).