Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Profit / Loss on Matrix Table in power BI

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

praveenkumarv

Helper II

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-05-2018
09:08 AM

Dear all,

I have stuck in calculating the Profit / Loss between two subgroups. I am pulling data from a single column and creating subgroup and group in power BI and drag that groups to Matrix table. But, I need to do some calculations as mentioned below, Please show me a way to achieve this result and it would be much appreciated.

1. Sum of Net Revenue **-** Sum of Cost of Revenue = **Gross Profit** (on a separate Total)

2. Sum of Other expenses **+ Gross Profit** = **Profit Other Expenses**

3. Sum of Other income + **Profit Other Expenses = Profit Before Tax**

4. Sum of Tax + **Profit Before Tax = Profit (Loss)**

**Note: **We are using the formula for subtracting --> Sum of Net Revenue - Sum of Cost of Revenue = Gross Profit

Gross Profit = IF (

ISFILTERED ( sortedGroup[Column1 (groups)] ),

SUMX ( Sheet1, Sheet1[Amount] ),

SUMX (

FILTER (Sheet1,Sheet1[Account Reporting (groups)]= "Net Revenue" ),

Sheet1[Amount]

)

- SUMX (

FILTER (

Sheet1,

Sheet1[Account Reporting] = "Cost of Revenue"

),

Sheet1[Amount]

)

)

Many Thanks,

Praveen

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

v-jiascu-msft

Microsoft

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-07-2018
01:51 AM

Hi Praveen,

1. Add an index.

Index = SWITCH ( [Account Balance sheet (groups)], "Group A", 1, "Group B", 2, "Group C", 3, "Group D", 4, 9999 )

2. Create a measure.

Measure = IF ( ISFILTERED ( 'Single Column Data'[Account Balance sheet (groups)] ), SUM ( 'Single Column Data'[Amount] ), CALCULATE ( SUMX ( 'Single Column Data', IF ( [Index] IN { 2, 3 }, -1 * 'Single Column Data'[Amount], 'Single Column Data'[Amount] ) ), FILTER ( ALL ( 'Single Column Data' ), 'Single Column Data'[Index] <= MAX ( 'Single Column Data'[Index] ) ) ) )

Best Regards,

Dale

Community Support Team _ Dale

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

If this post

11 REPLIES 11

Highlighted
##

ImkeF

Super User III

Re: Profit / Loss on Matrix Table in power BI

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-06-2018
02:18 AM

You have to adjust your Group-tables a bit and then these measures will deliver the desired result:

P&L = IF(ISBLANK([RunningTotal]), SUM(Sheet1[Amount]), [RunningTotal])

with RunningTotal like this:

RunningTotal = IF ( ISFILTERED ( sortedGroup[Index] ), BLANK (), CALCULATE ( [AmountWithSigns], FILTER ( ALL ( sortedGroup ), sortedGroup[Index] <= MAX ( sortedGroup[Index] ) ) ) )

and AmountWithSigns like this:

AmountWithSigns = SUMX(Sheet1, Sheet1[Amount] * RELATED(sortedGroup[SwitchSignMultiplicator]))

Of course, you could put it all together into one measure, but I find this method easier to follow (see table in attached file).

Also, you might want to use AmountWithSigns for other purposes as well once you're realized its usefulness.

Link to file: https://1drv.ms/u/s!Av_aAl3fXRbehbJbXqn84pFD2Wc8fg

Imke Feldmann (The BIccountant)

**If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!**

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Highlighted
##

v-jiascu-msft

Microsoft

Re: Profit / Loss on Matrix Table in power BI

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-06-2018
02:28 AM

Hi Praveen,

Please also try this formula. It worked base on your sample.

Measure = IF ( ISFILTERED ( sortedGroup[Column1] ), SUM ( Sheet1[Amount] ), CALCULATE ( SUMX ( Sheet1, IF ( Sheet1[Account Reporting (groups)] = "Cost of Revenue", -1 * Sheet1[Amount], Sheet1[Amount] ) ), FILTER ( ALL ( sortedGroup[Index] ), sortedGroup[Index] <= MAX ( sortedGroup[Index] ) ), ALL ( MajorGroup ) ) )

Best Regards,

Dale

Community Support Team _ Dale

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

If this post

Highlighted
##

praveenkumarv

Helper II

Re: Profit / Loss on Matrix Table in power BI

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-06-2018
10:11 AM

Hello Dale,

Thank you so much for your solution. We got the answer for our sample data. We gave that sample data in a sorted manner for your understanding only. But, in real, what I am expecting is - a solution for non-sorted dataset. Please ref the below screenshot for your understanding and also I have attached an updated pbix file. We need solution for this pbix file. How to do that, please help us. It is so confusing. Your timely help would help us a lot.

Many Thanks,

Praveen.

Highlighted

v-jiascu-msft

Microsoft

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-07-2018
01:51 AM

Hi Praveen,

1. Add an index.

Index = SWITCH ( [Account Balance sheet (groups)], "Group A", 1, "Group B", 2, "Group C", 3, "Group D", 4, 9999 )

2. Create a measure.

Measure = IF ( ISFILTERED ( 'Single Column Data'[Account Balance sheet (groups)] ), SUM ( 'Single Column Data'[Amount] ), CALCULATE ( SUMX ( 'Single Column Data', IF ( [Index] IN { 2, 3 }, -1 * 'Single Column Data'[Amount], 'Single Column Data'[Amount] ) ), FILTER ( ALL ( 'Single Column Data' ), 'Single Column Data'[Index] <= MAX ( 'Single Column Data'[Index] ) ) ) )

Best Regards,

Dale

Community Support Team _ Dale

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

If this post

Highlighted
##

sjoshi

Helper I

Re: Profit / Loss on Matrix Table in power BI

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-07-2018
10:53 PM

Hi Dale,

We are trying to achieve everthing from fact table and modified your consolidated measure but it is showing same data as NewAmountMST_ICS column not sure what is incorrect. MasterOrderIS is column in fact table holds 1, 2, 3, 4 etc values for each group.

ACCOUNTREPORTINGGROUP_INCOMESTATEMENTSSuperGroup: Revenue( consists of Net Revenue, Cost Revenue), Operating Expenses

ACCOUNTREPORTINGGROUP_INCOMESTATEMENTSGroup : Net Revenue , Cost of Revenue, Operating Expenses

MasterOrderIS column calulated based on each group: 1(Net Revenue), 2(Cost of Revenue), 3( Operating Expenses)

ACCOUNTREPORTINGGROUP_INCOMESTATEMENTS : Variables inside each group

NMeasure =

IF (

ISFILTERED ( 'LedgerTrans Fact'[ACCOUNTREPORTINGGROUP_INCOMESTATEMENTSGroup] ),

SUM ( 'LedgerTrans Fact'[NewAmountMST_ICS] ),

CALCULATE (

SUMX (

'LedgerTrans Fact',

IF (

'LedgerTrans Fact'[MASTERORDERIS] IN { 2, 3 },

-1 * 'LedgerTrans Fact'[NewAmountMST_ICS],

'LedgerTrans Fact'[NewAmountMST_ICS]

)

),

FILTER (

ALL ( 'LedgerTrans Fact'),

'LedgerTrans Fact'[MASTERORDERIS] <= MAX ( 'LedgerTrans Fact'[MASTERORDERIS] )

)

)

)

Thanks

Smita

Highlighted
##

Community Support Team _ Dale

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

v-jiascu-msft

Microsoft

Re: Profit / Loss on Matrix Table in power BI

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-09-2018
06:31 PM

Hi Smita,

Do you have the exact same data?

Best Regards,

Dale

If this post

Highlighted
##

sjoshi

Helper I

Re: Profit / Loss on Matrix Table in power BI

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-09-2018
06:46 PM

Yes Dale data is same. Index columns which are derived in SortedGroup and Major Group are avaliable in LedgerTrans fact table itself.

MasterOrdeIS: Is to order by Each group. like column1, Index column from SortedGroup table

Each Account Reporting Group is sorted based on this column.

New Order: Is to order by each super group. Like column1 and Index column from Major Group table.

Each Account Reportinf Super group table is ordered based on this column

neworder = IF('LedgerTrans Fact'[ACCOUNTREPORTINGGROUP_INCOMESTATEMENTSGroup]="Net Revenue",1,

IF('LedgerTrans Fact'[ACCOUNTREPORTINGGROUP_INCOMESTATEMENTSGroup]="Operating Expenses",2,

IF('LedgerTrans Fact'[ACCOUNTREPORTINGGROUP_INCOMESTATEMENTSGroup]="Cost of Revenue",1,

IF('LedgerTrans Fact'[ACCOUNTREPORTINGGROUP_INCOMESTATEMENTSGroup]="Other income, (expense), net",3,

IF('LedgerTrans Fact'[ACCOUNTREPORTINGGROUP_INCOMESTATEMENTSGroup]="Provision for income taxes",4,

IF('LedgerTrans Fact'[ACCOUNTREPORTINGGROUP_INCOMESTATEMENTSGroup]="Check",5,

IF('LedgerTrans Fact'[ACCOUNTREPORTINGGROUP_INCOMESTATEMENTSGroup]="Unallocated IT",6,

IF('LedgerTrans Fact'[ACCOUNTREPORTINGGROUP_INCOMESTATEMENTSGroup]="Unallocated Facilities",7,

IF('LedgerTrans Fact'[ACCOUNTREPORTINGGROUP_INCOMESTATEMENTSGroup]="Unallocated Benefits",8,

IF('LedgerTrans Fact'[ACCOUNTREPORTINGGROUP_INCOMESTATEMENTSGroup]="Dept 0000 Costs",9,

IF('LedgerTrans Fact'[ACCOUNTREPORTINGGROUP_INCOMESTATEMENTSGroup]="Dept Blank Costs",10)))))))))))

MasterOrderIS: This is order by column to order

Highlighted
##

Community Support Team _ Dale

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

v-jiascu-msft

Microsoft

Re: Profit / Loss on Matrix Table in power BI

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-11-2018
02:25 AM

Hi @sjoshi,

I don't know how you plot the measure in a visual. But if you group the values like that, it could be a different question. The values will be grouped together. I would suggest you create a new thread in this forum with a sample file.

Best Regards,

Dale

If this post

Highlighted
##

sjoshi

Helper I

Re: Profit / Loss on Matrix Table in power BI

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-11-2018
04:45 PM

Sure Dale I will. Thanks for all your help

Smita

Announcements

Visit our Community Blog for articles, guides, and information created by fellow community members.

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors

User | Count |
---|---|

329 | |

200 | |

112 | |

72 | |

62 |

Top Kudoed Authors

User | Count |
---|---|

374 | |

275 | |

153 | |

81 | |

80 |