cancel
Showing results for
Did you mean:
Frequent Visitor

## Created a Gross Profit Measure and it worked but fail to show in the matrix table

I am working on a Profit and Loss statement.  I have a structure table with 3 levels, highest level is the report group, SALES, COGS, GROSS PROFIT, SGA, OPERATING INCOME, NON-OP INCOME, INCOME BEFORE TAX, TAX and NET INCOME.  The lowest level is GL level which link with the Fact table.  The middle level is Sub-Grouping. I used a Switch formula to pickup numbers at the highest level.  SALES, CGS and others that have lowest level all showed up with numbers.

I then proceed to create measure for GROSS PROFIT, OPERATING INCOME etc because they derived from calculation based on SALES and COGS.  The Gorss Profit measure worked on its own.  However, the SWITCH did not pick it up in the report.  I don't think it is because the Switch measure is in error because if I just hard code some random value in the Gross Profit Measure, the random number will actual showup in report.

As you can see the GROSS PROFIT actual calculated the correct amt, but not showing in report line.

If I just replace the DAX formula in Gross Profit to some random number like 123456, GROSS PROFIT line will show.

anyone know why is it?  Appreciated.

2 ACCEPTED SOLUTIONS
Super User

Hi huaj1029

Let me explain why your solution is not giving the desired results and try help fix it ...

In your visual you have 3 columns and the 1st column is Lv3 (Sales, Cogs, Gross Profit etc)

In your dax measure you have an OR command for Sales or Cogs.

This logic will be true for the Sales row. 😀

This logic will be true for the Cogs row.  😀

This logic is false for the Gross Profit row. 😮
Hence you get blanks for Gross Profit . Understand ????

I assume that you have already created measures for [Sales] and [Cogs]

In which case you simply need

Gross Profit=

[Sales] - [Cogs]

It is that easy.

Please click thumbs up and accept as solution button. Thank you !  😎

Super User

Hi again huaj1029

Hhhmmm well it would help if you could share the measures and data I assume that you have code like this ...

Sales =

CALCULATE (
SUM(PL[Value]),
"PL ACCT GROUP"[LV3] = "Sales"
)

COGS =

CALCULATE (
SUM(PL[Value]),
"PL ACCT GROUP"[LV3] = "Gogs"
)

Gross Profit =
[Sales] - [Cogs]

Obviosuly,

sales will return sales on the sales row.

cogs will return cogs on the cof row.

but gross profit will return blanks on the gross profit row because it is not the sales or cogs row !!! Understand ???

Try this ...

Gross Profit =
SUMX("PL ACCT GROUP", [Sales] - [Cogs]),
ALL("PL ACCT GROUP")

The ALL removes the row filter and SUMX interates the sales and cogs calculation for every row.

Hope this solves it !

You are only showing partial screen prints.

We cant see the all your dax meassures or the "driver" table that is being used for the LV3 columns.

Please can you copy and paste some non-confidential data in Enter Data, and then change you data source to use that. Then save the PBIX on Onedrive with share read access and post the link here so solvers can take a look.

Dont send the PBIX without using the Enter Data trick, because we wont be abble to access you data via the firewalls. Whereas anayone can see the data in Enter Data tables.

😀

7 REPLIES 7
Super User

Hi again huaj1029

Hhhmmm well it would help if you could share the measures and data I assume that you have code like this ...

Sales =

CALCULATE (
SUM(PL[Value]),
"PL ACCT GROUP"[LV3] = "Sales"
)

COGS =

CALCULATE (
SUM(PL[Value]),
"PL ACCT GROUP"[LV3] = "Gogs"
)

Gross Profit =
[Sales] - [Cogs]

Obviosuly,

sales will return sales on the sales row.

cogs will return cogs on the cof row.

but gross profit will return blanks on the gross profit row because it is not the sales or cogs row !!! Understand ???

Try this ...

Gross Profit =
SUMX("PL ACCT GROUP", [Sales] - [Cogs]),
ALL("PL ACCT GROUP")

The ALL removes the row filter and SUMX interates the sales and cogs calculation for every row.

Hope this solves it !

You are only showing partial screen prints.

We cant see the all your dax meassures or the "driver" table that is being used for the LV3 columns.

Please can you copy and paste some non-confidential data in Enter Data, and then change you data source to use that. Then save the PBIX on Onedrive with share read access and post the link here so solvers can take a look.

Dont send the PBIX without using the Enter Data trick, because we wont be abble to access you data via the firewalls. Whereas anayone can see the data in Enter Data tables.

😀

Frequent Visitor

thanks.  I tried using All, it worked.  Much appreciated.

Super User

Hi huaj1029

Let me explain why your solution is not giving the desired results and try help fix it ...

In your visual you have 3 columns and the 1st column is Lv3 (Sales, Cogs, Gross Profit etc)

In your dax measure you have an OR command for Sales or Cogs.

This logic will be true for the Sales row. 😀

This logic will be true for the Cogs row.  😀

This logic is false for the Gross Profit row. 😮
Hence you get blanks for Gross Profit . Understand ????

I assume that you have already created measures for [Sales] and [Cogs]

In which case you simply need

Gross Profit=

[Sales] - [Cogs]

It is that easy.

Please click thumbs up and accept as solution button. Thank you !  😎

Frequent Visitor

Dear Speedramps,

Not so fast.  I did exactly what you suggested in the very begining.  it did not work either.

If you want to see proof, here it is.

The OR command gave me the same result because by lumping COGS and SALES gave me the same result because I had Sales and COGS in their natural signs.

Frequent Visitor

your suggestion worked.  Using All to get rid of all the row filters.  much appreciated

Super User

Thank you huaj1029 for accepting my solution. More importantly I hope you now understand the natural row filter context in table visual eg COGS.  If you want to include different r

Frequent Visitor

Hi Speedramps,

here is my relationship map and Acct Grp table.  The actual table Subline, which is the account detail level, mapped to the lowest level in the Account grp tbl at Lv5.  Lv3 is the highest aggregate level.  I don't have measure to calculate Sales or COGS because they are defined at Lv3 which can be subtatal can be derived from Lv5.  Gross Margin, also a Lv3, I just aggregate Lv3 Sales and COGS.  It worked and had correct amt.  however when you insert into the Switch, it shows blank.  As I said, the Switch formula had to work because when I replace the measure with random value, it came through in the P&L.