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)
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)