none
Measure that respects Pivot's Table's filter context. RRS feed

  • Question

  • I hope one of you wizards can help me fix my measure.

    I’m using PP with Excel 2013

    Source of problem: I can’t relate the Group that resides in Actual to Budget (Can’t create a relationship)

    Solution needed: A measure that will only sum if Group & Ledger selected in Pivot match.

    I thought using VALUES would work but I'm doing something wrong or choosing  the wrong context or combination.

    I think I’m very close to the solution. Any assistance you can provide would be most welcome!

    Parent measure:

    Cust Lvl Budgeted Sales Amount :=
    
    SUM ( 'Customer Level Budget Data'[Cust Lvl Budget Sales
    Amount] )

    I have tried the following for Budgeted Sales:

    Budgeted Sales - Cust Lvl :=
    
    CALCULATE ( [Cust Lvl Budgeted Sales Amount], 'Raw Actual' )
    
     
    
    Budgeted Sales - Cust Lvl :=
    
    SUMX (
    
        VALUES ( 'Raw
    Actual'[Business Unit] ),
    
        [Cust Lvl Budgeted
    Sales Amount]
    
    )
    
     
    
    Test Budgeted Sales :=
    
    SUMX ( VALUES ( 'Raw Actual'[Business Unit] ), [Budgeted
    Sales - Cust Lvl] )
    
     
    
    Test Budgeted Sales 2 :=
    
    SUMX ( VALUES ( tblBUlookup[Business Unit] ), [Budgeted
    Sales - Cust Lvl] )
    
     
    
    Test Budgeted Sales 3 :=
    
    SUMX ( VALUES ( tblBUlookup[Group] ), [Budgeted Sales - Cust
    Lvl] )


    diagram view
    • Edited by Little_BB Monday, April 30, 2018 2:52 PM
    Wednesday, April 25, 2018 7:37 PM

Answers

  • Thank you for all your help Marcin, Willson, & Matt!!

    Issue was solved with a data model change! Now that both tables are filtered by the same lookup, 'regular' measures work perfectly.

    Solution: Data Model Changed

    • Marked as answer by Little_BB Thursday, May 3, 2018 3:31 PM
    Thursday, May 3, 2018 3:30 PM

All replies

  • Hi Little_BB,

    Thanks for your question.

    To solve your question more efficiently, would you mind typing out 5-10 rows of example data for these table, then showing what results you are expecting based on those sample data? It is much better if you can share the EXCEL work book. Do mask sensitive data before uploading.

    If I understand you correctly, what you are talking about is Disconnected Slicers. If so, please refer to below blog:
    https://www.fourmoo.com/2017/08/08/power-bi-how-disconnected-slicers-can-enrich-transform-your-analytics-data/


    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

    Thursday, April 26, 2018 5:10 AM
    Moderator
  • Have a read of my article here. https://exceleratorbi.com.au/virtual-filters-using-treatas/ You can’t use treatas in Power Pivot, but you can use intersect instead.

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    Thursday, April 26, 2018 9:25 AM
    Answerer
  • Thank you Willsun & Matt!

    They still haven't verified my account so adding pictures to my post is still not working. I can't provide the actual file but have several pictures that will help explain the problem and what I've tried.

    For now I will review both of your links, thank you.

    Thursday, April 26, 2018 1:29 PM
  • Test Budgeted Sales - Cust Lvl :=
    IF (
        [Actual Sales - Cust Lvl] <> 0,
        CALCULATE ( [Cust Lvl Budgeted Sales Amount], 'Raw Actual' )
    )
    

    I just tried the above as a test but the sub totals don't equal properly for the measure at that point. I learned from Matt's book that Subtotals are basically the absence of filters.
    Thursday, April 26, 2018 2:29 PM
  • You likely need sumx. https://exceleratorbi.com.au/use-sum-vs-sumx/

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    Thursday, April 26, 2018 9:19 PM
    Answerer
  • Figured out how to obtain data that should let me link the customer level data to the actual. Will play with it more to see if it resolves the issue.
    Thursday, April 26, 2018 9:31 PM
  • Hi Little_BB,

    Thanks for your question.

    In this scenario, please try below DAX formula:

    Test Budgeted Sales 1 :=
    SUMX (
        VALUES ( tblBUlookup[Group] ),
        IF ( ISBLANK ( [Actual Sales - Cust Lvl] ), 0, [Cust Lvl Budgeted Sales Amount] )
    )


    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, April 30, 2018 6:47 AM
    Moderator
  • Thank you Willson! I've updated my post with the diagram view picture.

    I've since made some changes that have let me get the data at the same grouping.  I will post an updated picture after I tinker with it some more.

    Monday, April 30, 2018 2:54 PM
  • trying slightly different approach here - can you use virtual relationships?
    as described here:
    http://sqlblog.com/blogs/marco_russo/archive/2016/07/26/leverage-intersect-to-apply-relationships-in-dax.aspx

    Monday, April 30, 2018 3:41 PM
  • I was able to add the correct Group # at the Customer Level. However, I can’t get the pivot to display properly.

    The data needs to display with the Grouping view inside tblBUlookup because the customer level doesn’t have certain things included in it.

    But when I attempt to utilize the ‘mothership’ lookup table, I get the error message about not being able to activate the relationship.

    Do I need to make a different lookup table or modify the budget measure to make it work?

    Updated diagram view

    Monday, April 30, 2018 4:13 PM
  • trying slightly different approach here - can you use virtual relationships?
    as described here:
    http://sqlblog.com/blogs/marco_russo/archive/2016/07/26/leverage-intersect-to-apply-relationships-in-dax.aspx


    Thank you Marcin.  I'm reviewing the link to see if it will work. By adding group data in at customer level, I have probably messed up the entire problem.
    Monday, April 30, 2018 4:15 PM
  • I have a workaround to the grouping but it is really not what I'm looking for.  Sharing in case it helps someone else at some point.

    My budget Sales numbers are still incorrect. Data shows when it shouldn't and doesn't show when it should.  I think this might be due to the fact that calculate is being filtered by the Raw Actual table. This removes budget numbers if nothing actual was recorded. How to solve this problem...still thinking and open to ideas. Also still reviewing information provided earlier.

    Layout workaround

    Monday, April 30, 2018 6:55 PM
  • I tried Willson's idea and get the results shown in the screenshot below.  I think I need some type of advanced calculate using filter or all but I don't have experience successfully using them yet.

    Edit: I don't think this is a granularity issue because each data table has a link to the calendar at the correct monthly granularity. The issue seems to be a problem with properly relating the data and/or how I'm attempting to get the measure to work.Willson's Idea



    • Edited by Little_BB Monday, April 30, 2018 8:29 PM
    Monday, April 30, 2018 8:18 PM
  • Hi Little_BB,

    Thanks for your question.

    In this scenario, please replace 0 with blank() , if you do not want display with 0:

    Test Budgeted Sales 1 :=
    SUMX
    (
        VALUES
    ( tblBUlookup[Group] ),
        IF
    ( ISBLANK ( [Actual Sales - Cust Lvl] ), BLANK(), [Cust Lvl Budgeted Sales Amount] )
    )


    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

    Tuesday, May 1, 2018 4:48 AM
    Moderator
  • In this scenario, please replace 0 with blank() , if you do not want display with 0:

    Test Budgeted Sales 1 :=
    SUMX
    (
        VALUES
    ( tblBUlookup[Group] ),
        IF
    ( ISBLANK ( [Actual Sales - Cust Lvl] ), BLANK(), [Cust Lvl Budgeted Sales Amount] )
    )


    Best Regards
    Willson Yuan


    This helped a lot Willson, thank you!

    Only two issues remain:

    1. Sub Totals aren't adding up to the correct value.

    2. Because the measure looks for Actual Sales value, if it doesn't find it, it doesn't display budgeted but not sold. I might be able to use an OR operator somehow with this. (||) 

    I'll keep experimenting, thank you again!

    Willson's idea modified to return blank

    Tuesday, May 1, 2018 2:23 PM
  • Modified Willson Budgeted Sales Test :=
    SUMX (
        VALUES ( tblBUlookup[Group] ),
        IF (
            ISBLANK ( [Actual Sales - Cust Lvl] ) && [Budgeted Sales - Cust Lvl],
            BLANK (),
            [Budgeted Sales - Cust Lvl]
        )
    )

    Tried the above but get the same data as the non-modified Willson. This may be due to the values I want already being filtered.

    The budgeted Sales-Cust Lvl measure is:

    Budgeted Sales - Cust Lvl :=
    CALCULATE (
        SUM ( 'Customer Level Budget Data'[Cust Lvl Budget Sales Amount] ),
        'Raw Actual'
    )


    Is the 'Raw Actual' filter even needed now that we are using VALUES and SUMX...

    Modified the measure to:

    Budgeted Sales - Cust Lvl :=
    SUM ( 'Customer Level Budget Data'[Cust Lvl Budget Sales Amount] )

    But get this. Not the solution:Another way that it doesn't work. :)

    Tried changing it to a sumx but get the same results as above.

    Budgeted Sales - Cust Lvl :=
    SUMX ( 'Customer Level Budget Data', [Cust Lvl Budget Sales Amount] )

    Tried the calculate without a filter but same results as above.

    Budgeted Sales - Cust Lvl:=CALCULATE(SUM('Customer Level Budget Data'[Cust Lvl Budget Sales Amount]))

    Also tried a SUMX inside the calculate. That gave me blanks on most everything.

    Budgeted Sales - Cust Lvl :=
    CALCULATE (
        SUMX ( 'Customer Level Budget Data', [Cust Lvl Budget Sales Amount] )
    )

    Tried leaving the SUMX and adding the Raw Actual Filter back in. Same mostly positive results as before. However, still no Budgeted but not sold data and the subtotals don't add properly.

    I'm open to and welcome any ideas.

    Budgeted Sales - Cust Lvl:=CALCULATE(SUMX('Customer Level Budget Data',[Cust Lvl Budget Sales Amount]),'Raw Actual')

    Note: I have the following books if you want me to look at something specifically:

    Power Pivot & Power BI (2nd Ed) Rob Collie

    Power Pivot Alchemy Rob Collie and Bill Jelen

    Learn to Write DAX Matt Allington

    I figured out that the reason I get the dotted line is because the relationship sort of 'circles around'. I can delete the connection between Raw Actual and & AB Lookup. If I do that I can make a solid line connection but it breaks too many other things in the report.

    So, I'm still trying to figure out the magic measure.

    Learned about USERELATIONSHIP() from Rob's book pg 219.  That allows me to use a dotted line relationship in a measure.  However, I'm not sure how that really helps me obtain accurate data.

    I've begun experimenting with changing relationships around now.





    • Edited by Little_BB Tuesday, May 1, 2018 8:48 PM
    Tuesday, May 1, 2018 2:48 PM
  • Could you explain the logic once again? It it not clear to me from our pictures. As I understand there are 3 tables:
    'Customer Level Budget Data'
    with measure 

    Cust Lvl Budgeted Sales Amount :=
    SUM ( 'Customer Level Budget Data'[Cust Lvl Budget Sales Amount] )

    'Raw Actuals'

    'tblBUlookup'The way I understand it you want to filter the first table based on selection in one of the other tables - is that correct?
    What is the common dimension there? Can you paste here an example of top few rows from each table? Just the structures, numbers can be made up

    Wednesday, May 2, 2018 9:21 AM
  • Thank you Marcin!  I appreciate you taking time to help!  Pretty close. Hopefully the next screenshot explains better.
    • They need grouping done with this Business Unit but there is no connection to the Customer Level Budget Data.
    • So Budgeted data returns for all Sub Ledger values regardless of the initial filter context of the pivot
    • I understand why it isn't working but don't know the best way to solve the problem, without restructuring the data model and breaking the other 95% of my work.

      My thought is that a measure can account for this type of crazy situation. But, I need some help figuring it out. :)

    Better explaination for Marcin

    Change Row Layout and the 'regular' measure works.
    • Edited by Little_BB Wednesday, May 2, 2018 2:16 PM
    Wednesday, May 2, 2018 2:02 PM
  • can you try this one?
    Budgeted Sales - Cust Lvl:=
    CALCULATE (
        [Cust Lvl Budgeted Sales Amount],
        INTERSECT(
            ALL('Customer Level Budget Data'[SubLedger]),
            VALUES('Raw Actuals'[Sub Ledger])
        )
    )
    Wednesday, May 2, 2018 2:21 PM
  • can you try this one?
    Budgeted Sales - Cust Lvl:=
    CALCULATE (
        [Cust Lvl Budgeted Sales Amount],
        INTERSECT(
            ALL('Customer Level Budget Data'[SubLedger]),
            VALUES('Raw Actuals'[Sub Ledger])
        )
    )

    Thank you Marcin!  I get a message saying: This is not a valid DAX expression.  I'm using Excel 2013. For some reason intellisense doesn't show me data on INTERSECT either. Perhaps this is due to my version?

    Marcin's Idea

    I've confirmed that INTERSECT will work in PowerBI and 2016 only.

    What about something like this:

    [Filtered Measure] :=
    CALCULATE (
        <target_measure>,
        FILTER (
            ALL ( <target_granularity_column> ),
            CONTAINS (
                VALUES ( <lookup_granularity_column> ), 
                <lookup_granularity_column>,
                <target_granularity_column> 
            )
        )
    )
    Is there a way to use the above pattern you linked to earlier?
    • Edited by Little_BB Wednesday, May 2, 2018 3:23 PM
    Wednesday, May 2, 2018 3:14 PM
  • it would be something like this:

    Budgeted Sales - Cust Lvl:=
    CALCULATE (
        [Cust Lvl Budgeted Sales Amount],
        FILTER(
            ALL('Customer Level Budget Data'[SubLedger]),
            CONTAINS(
                VALUES('Raw Actuals'[Sub Ledger]),
                'Raw Actuals'[Sub Ledger],
                'Customer Level Budget Data'[SubLedger]
            )
        )
    )

    Wednesday, May 2, 2018 3:35 PM
  • it would be something like this:

    Budgeted Sales - Cust Lvl:=
    CALCULATE (
        [Cust Lvl Budgeted Sales Amount],
        FILTER(
            ALL('Customer Level Budget Data'[SubLedger]),
            CONTAINS(
                VALUES('Raw Actuals'[Sub Ledger]),
                'Raw Actuals'[Sub Ledger],
                'Customer Level Budget Data'[SubLedger]
            )
        )
    )

    Thank you SO much Marcin!!  I think that made it so I'm almost there!  I will experiment some more and report back!

    Wednesday, May 2, 2018 4:33 PM
  • I've read this over several times and am working to understand what is happening:

    Snipped From SQLBI

    In general, you apply the following template to return a measure filtered by applying a logical relationship through columns that are not keys in any of the tables involved, using these markers:

    • <target_measure> is the measure defined in the target table.
    • <target_granularity_column> is the column that defines the logical relationship in the table that contains rows that should be filtered by the relationship.
    • <lookup_granularity_column> is the column that defines the logical relationship in the lookup table, which should propagate the filter to the target table.

    *end snip*

    My understanding of what the pattern is doing:

    I'm telling CALCULATE to run a SUM on whats left of the budget table AFTER it has taken ALL values in the budget  table and removed everything that doesn't match a sub ledger value inside the Actuals table. 

    Is my understanding correct? If not, please help me understand better.

    If the understanding is accurate, the reason I don't have values from the budget using the pattern is because they were filtered out when there was no match with Actuals.

    How do I get it to provide everything grouped by Business Unit while it is only connected to Actuals?

    Wednesday, May 2, 2018 7:33 PM
  • pretty much, the only thing is that it operates on columns rather than tables.
    Which table would have all the SubLedger entries? Is it 'AB lookup combination'? does it have to be filtered by [Business Unit] from actuals?
    Thursday, May 3, 2018 8:29 AM
  • Thank you for all your help Marcin, Willson, & Matt!!

    Issue was solved with a data model change! Now that both tables are filtered by the same lookup, 'regular' measures work perfectly.

    Solution: Data Model Changed

    • Marked as answer by Little_BB Thursday, May 3, 2018 3:31 PM
    Thursday, May 3, 2018 3:30 PM