# Can anyone help me on this?

• ### Question

• I have two tables.
PivotVwMain - this has the data I need to sum up
tblReportStruct - this is a table containing that looks like this:

FSLineItemDisplayTxt isCalculated
Sales 0
COS 0
Gross margin 1

The Sales and COS exist in the main data table and the two tables are linked by the FSLineItemDisplayTxt so the formula below correctly sums up the sales and COS.

However the gross margin does not exist in the main table, instead I want to sum up Sales and COS together to get them, which I presume means removing the FSLineItemDisplayTxt filter as I tried to do below but it isn't working

Measure 1:=IF(Sum(tblReportStruct[IsCalculated])=1,CALCULATE(Sum(PivotVwMain[Amount]),ALL(tblReportStruct[FSLineItemDisplayTxt])) ,Sum(PivotVwMain[Amount]))

Can anyone help me out here?
There is a second part to this question though starting with the above would be a good first step.
The main table has more than just Sales and CO and I have a third table that I need to somehow use which tells me which rows from the main data I need to sum of to calculate Gross margin.
This table looks like:
FSLineItemDisplayTxt maintableID
Gross margin 22 (i.e. Sales)
Gross margin 23 (i.e. COS)
Saturday, September 28, 2013 9:34 PM

• Hi,

 FSLineItemDisplayTxt FSLineItemDisplayTextGroup Sales Gross margin COS Gross margin

then you can create a hierarchy as
Gross margin
# Sales
# COS

this hierarchy would automatically work similar to your current calculation but without requiring any DAX

to solve your second question you may only need to extend this table?

hth,
gerhard

- www.pmOne.com -

Thursday, October 24, 2013 9:33 AM

### All replies

• Any suggestions for PG2?

Thanks!

Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

Thursday, October 24, 2013 8:39 AM
• Hi,

 FSLineItemDisplayTxt FSLineItemDisplayTextGroup Sales Gross margin COS Gross margin

then you can create a hierarchy as
Gross margin
# Sales
# COS

this hierarchy would automatically work similar to your current calculation but without requiring any DAX

to solve your second question you may only need to extend this table?

hth,
gerhard

- www.pmOne.com -

Thursday, October 24, 2013 9:33 AM