Forum Discussion

Andrew_SSFM's avatar
Andrew_SSFM
Frequent Visitor
7 years ago
Solved

Do not sum certain columns in matrix. Show values based on filter level?

Hello All!   I have some project data coming from two different tables and one of those tables (Project) has values (Fee, Complete) that should not be summed.  All values are attached to a 3-tier w...
Need Help
  • d_gosbell's avatar
    d_gosbell
    7 years ago

    Actually if you fix the syntax errors in my previous code the same approach will work for both the fee and pcnt (assuming that the row with the blank WBS2 is a total row so it should be shown for the WB1 line and hidden as a WBS2 line

     

    Fee Amt =
    IF( HASONEVALUE(Data[WBS2])
       ,CALCULATE(SUM(Data[Fee]), FILTER(VALUES(Data[WBS2]),Data[WBS2] <> ""))
       ,CALCULATE(SUM(Data[Fee]),Data[WBS2] ="" )
    )
d_gosbell's avatar
d_gosbell
Icon for Super User rankSuper User
7 years ago

You could probably do this in a measure, using HASONEVALUE to detect if the current context is showing data drilled down to WBS2 or not and then either filter to only include or exclude the row with a blank WBS2 code. (I'm filtering based on an empty string, but if that does not work you might have to try using ISBLANK() depending on how your data is loaded)

 

Fee Amt = IF( HASONEVALUE(Project[WBS2]) 
      ,SUM(Project[Fee], FILTER(VALUES(Project[WBS2]) <> "")
     ,SUM(Project[Fee], FILTER(VALUES(Project[WBS2]) = "")  
)