How to calculate gross profit

[Mananj91]

I am trying to create a report like this but I am facing issue with calculating % values. Here in Club Bar and Lounge the top three rows are "sales" and the bottom 6 rows are "cost". So, the percent next to "Sales" when add up comes to 100% and percent next to "costs" is using a formula as cost value/total sale(i.e sum of three sales shown in that location)

Can someone please help me to find a DAX formula for this type of issues?

Thank you.

[v-lili6-msft]

Hi,@ Mananj91 

You need to add a column to distinguish between “sales” and “cost”,

and add a measure like this;

% = SUM(Table6[Qty])/CALCULATE(SUMX(FILTER(ALL(Table6),Table6[Type]="sales"),Table6[Qty]))

Then field into the matrix, and this is the result:

Best Regards,

Lin

[Mananj91]

Hi @v-lili6-msft

I have a quick question How are you only using total "%" in the report and it is not displayed in the rows. Please guide me through the steps.

Thank you in advance

[Seward12533]

What does your data look like.  If is fairly clean columns with dates, categories and values.

You dont need much more than a date table and a few measures.  Spend a little time learning the basics of the tool Its a different paradigm than Standard Excel 

You need a decent date table to link your transaction details to

Could probalby do this one date table and single measures

COGS = SUM(table[Cost])

REVENUE = SUM(table[Sales])

Just a warning is its its HARD to mix these in the same visual in teh table format your trying to use.  This is NOT the paradigmn of PowerBI.  Typcially this is done by showing TOTAL for period in a table with the categories (club bar, etc..) on the rows and your measures REVENUE, COGS, Margin etc on the colum with visuals showing the trends of each indivually (say stacked bars) of Revenue by Category.

If yuu NEED to produce somethign like this I recommend you do it in power query in Excel where you can build multiple pivot tables (same as matrix visuals) convert to formulas and build  dashboard like this.