locked
Can anyone help me on this? RRS feed

  • 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

Answers

  • Hi,

    how about traversing your table to the following:

    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
    Answerer

All replies

  • Any suggestions for PG2?

    Thanks!


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

    Answer an interesting question? Create a wiki article about it!

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

    how about traversing your table to the following:

    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
    Answerer