Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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.

 

huaj1029_0-1652570735805.png

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

 

huaj1029_1-1652570992690.png

anyone know why is it?  Appreciated.

2 ACCEPTED SOLUTIONS
speedramps
Super User
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 !  😎

 

 

View solution in original post

speedramps
Super User
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.

 

😀

View solution in original post

7 REPLIES 7
speedramps
Super User
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.

 

😀

Anonymous
Not applicable

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

speedramps
Super User
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 !  😎

 

 

Anonymous
Not applicable

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.

huaj1029_0-1652617144872.png

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.  

 

Anonymous
Not applicable

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

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

Anonymous
Not applicable

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.

 

huaj1029_0-1652654930589.png

 

huaj1029_1-1652655519236.png

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.