locked
DAX Measure to Respect Parent Filter of Codes RRS feed

  • Question

  • Hi everyone...

    This is hard to explain, and I am not even sure the title adequately explains the situation.  I created a sample workbook (Excel 2016) that shows the problem.

    I have two "fact" tables of different granularity.  One is a "budget" table, the other is an "Actual" table.  The budget has listing of codes (one or more) and their corresponding Estimated Budgets (Cost and Hours).  The Actuals are the actual cost and hours taken to complete the Code in question.  We add Codes every month and we are interested in a report of added budget codes this month and any actual cost / hours spent thus far.

    The issue is the "actuals spent thus far" that is tripping me up.  I can get the correct answer, but codes for past months are also showing up on the actual side.  Here's a screenshot:

    Notice the highlighted rows are from past "budgeted" items (Not in the month in question).  I would like to have a Cumulative Hours / Cost for JUST items added this month.

    This link, if I did this onedrive thing right, should link to a simple example workbook.

    Sample File

    Thanks in advance...

    John


    John Thomas

    Thursday, March 1, 2018 5:34 PM

Answers

  • Hi John,

    Thanks for your response.

    In this scenario, please try below DAX formula:

    This Month's Cumulative Actuals Cost :=
    SUMX (
        VALUES ( dCode[Code] ),
        IF (
            ISBLANK ( fBudget[Total Est. Cost] ) && ISBLANK ( fBudget[Total Est. Hours] ),
            BLANK (),
            [Cumulative Total Cost]
        )
    )
    
    This Month's Cumulative Actuals Hours :=
    SUMX (
        VALUES ( dCode[Code] ),
        IF (
            ISBLANK ( fBudget[Total Est. Cost] ) && ISBLANK ( fBudget[Total Est. Hours] ),
            BLANK (),
            [Cumulative Total Hours]
        )
    )
    



    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by alexander fun Tuesday, March 6, 2018 12:55 PM
    • Marked as answer by jbt_PwrPvt Tuesday, March 6, 2018 10:12 PM
    Tuesday, March 6, 2018 2:20 AM

All replies

  • Hi John,

    Thanks for your question.

    I can not download the shared sample workbook with your link.Would you mind sharing it again and make sure we can download?


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, March 2, 2018 5:38 AM
  • Hi Wilson... Sorry about that.  Here's another link.

    Sample File

    https://1drv.ms/f/s!AvxG8sbQyKTUiVUFnf63B3q9RL-n

    Thanks...



    John Thomas

    Saturday, March 3, 2018 11:15 AM
  • Excel 2016 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    Compare actual hours and costs with monthly set budget figures.
    With Slicer and PivotChart.
    All new, expanded, fictitious data base.
    http://www.mediafire.com/file/t6z1186v31i8b0v/03_03_18.xlsx
    http://www.mediafire.com/file/32hwq38y4ptarq8/03_03_18.pdf

    Saturday, March 3, 2018 9:57 PM
  • Herbert - Thanks for taking the time to put this together.  While it is an interesting approach, it does not solve the problem.  Your solution just takes into account a budget value by month, which is not the problem presented.  Please read my original post and take a look a the sample data again.  I just need a cumulative total of actual cost for the newly added budget ITEMS added in any given month.  Items as in "Code" A, B, C, X, Y, Z.  So a good solution would be my original Sample Report (See Screen Shot), with just B & C codes as they were the two which were added in February.  The problem is there were also Actual Cost transaction which were for previously added "Codes", but also had transactions in February. 

    In addition, this is just a "mock-up" of a much larger data model (5M+ records in the transaction tables).  So far, we are using Excel 2016 so we can't afford to use additional "helper" added columns to the transaction tables.  This solution needs to be eloquent and solved with a measure approach rather than calculated columns.

    Hope this helps.


    John Thomas




    • Edited by jbt_PwrPvt Sunday, March 4, 2018 6:01 PM
    Sunday, March 4, 2018 5:51 PM
  • Hi John,

    Thanks for your response.

    If I understand you correctly, please try below DAX formula:

    This Month's Cumulative Actuals Cost :=
    IF (
        ISBLANK ( fBudget[Total Est. Cost] ) && ISBLANK ( fBudget[Total Est. Hours] ),
        BLANK (),
        CALCULATE ( [Cumulative Total Cost], VALUES ( dCode[Code] ) )
    )
    
    
    This Month's Cumulative Actuals Hours :=
    IF (
        ISBLANK ( fBudget[Total Est. Cost] ) && ISBLANK ( fBudget[Total Est. Hours] ),
        BLANK (),
        CALCULATE ( [Cumulative Total Hours], VALUES ( dCode[Code] ) )
    )


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, March 5, 2018 2:58 AM
  • Wilson,

    While this is close, it's not quite correct.  Notice the Grand Total is 27,045.  That is the total for ALL cost of ALL codes over ALL months.  Correct total should be 12,385.


    John Thomas

    Monday, March 5, 2018 12:28 PM
  • Hi John,

    Thanks for your response.

    In this scenario, please try below DAX formula:

    This Month's Cumulative Actuals Cost :=
    SUMX (
        VALUES ( dCode[Code] ),
        IF (
            ISBLANK ( fBudget[Total Est. Cost] ) && ISBLANK ( fBudget[Total Est. Hours] ),
            BLANK (),
            [Cumulative Total Cost]
        )
    )
    
    This Month's Cumulative Actuals Hours :=
    SUMX (
        VALUES ( dCode[Code] ),
        IF (
            ISBLANK ( fBudget[Total Est. Cost] ) && ISBLANK ( fBudget[Total Est. Hours] ),
            BLANK (),
            [Cumulative Total Hours]
        )
    )
    



    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by alexander fun Tuesday, March 6, 2018 12:55 PM
    • Marked as answer by jbt_PwrPvt Tuesday, March 6, 2018 10:12 PM
    Tuesday, March 6, 2018 2:20 AM
  • That did the trick Wilson...  Thanks!

    John Thomas

    Tuesday, March 6, 2018 10:13 PM