Row Level Sum Sales Less COS = GP

[Anonymous]

I have two tables

1. Finance Data dumb table (Which is having Full Finance Transactions) 

2.  Ledgers grouping table separate.

Linked both table with common field of account code.

Grouping Table is having Mani Group Sales, Cost, Staff Expneses , Admin Exp, Depriciations

I need the Report Like

                                  Jan         Feb        Mar      Apr

Sales

Cost

GP (Sales-Cost)

GP %

Staff Exp

Admin Exp

Operating Profit 

(GP-StaffExp-Admin Exp

Depreciation

Profit 

(Operating Profit - Depreciation)

Please give some ideas about that

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

According to your description, Grouping Table has Mani Group Sales, Cost, Staff Expneses , Admin Exp, Depriciations, so what's the stage of the data? All the year round or current month? And what's in the Finance Data dumb table. Would you like to provide a simple demo to clirefy the problem?

Best Regards,
Community Support Team _ kalyj

[ReneMoawad · Accepted solution]

Hi,

First you needd to Create a new table by using "Enter Data", this table should include 2 columns (Description, Order)

in the Description column you need to add the below

• Sales
• Cost
• GP (Sales-Cost)
• GP %
• Staff Exp
• Admin Exp
• Operating Profit 
• (GP-StaffExp-Admin Exp
• Depreciation
• Profit 
• (Operating Profit - Depreciation)

and in the Order column you need to specify the order of each row from 1 to 11

After that, you need to click on the field Description, and change the Sort Column

After doing that, you need to create a measure for each of the description example: SalesMeasure = SUM(Sales), CostMeasure = SUM(Cost)...

The last measure that you will be using in the Matrix is the the below:

FinalResult = 

Var selectedDescription = SELECTEDVALUE(NewTable[Description])

Return

SWITCH(

    selectedDescription

    , "Sales", [SalesMeasure]

    , "Cost", [CostMeasure]

    , "GP (Sales-Cost)", [SalesMeasure] - [CostMeasure]

    , ...

)

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

According to your description, Grouping Table has Mani Group Sales, Cost, Staff Expneses , Admin Exp, Depriciations, so what's the stage of the data? All the year round or current month? And what's in the Finance Data dumb table. Would you like to provide a simple demo to clirefy the problem?

Best Regards,
Community Support Team _ kalyj