Forum Discussion
Custom grand total (exclude membership)
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
Hi Anonymous ,
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 ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
11 Replies
- MFelix
Super User
Hi Anonymous ,
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).
v-lionel-msftCommunity Support
Hi Anonymous ,
Like this?
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 Anonymous ,
Like this?
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
That works perfectly! Thank you so much for your help v-lionel-msft ! I can finally finish my report lol
