Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
That works perfectly! Thank you so much for your help @v-lionel-msft ! I can finally finish my report lol
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.
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.
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.
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!
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.
@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!
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.
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsIn 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
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).
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |