Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Running Total with If

Hi. I have a measure of running total of payments made for investments over time. I also have an input table that shows when my department owns the investments and when we don't (an "ownership Boolean" table, if you like). How do I modify the running total measure so that it only shows the running total when we own it?

The running total measure is (I've copied this from a forum and have adjusted it for my own use):

Cash Cost  =
VAR LastVisibleDate =
MAX ( 'Date'[Date] ) -- my date table and date column
VAR FirstVisibleDate =
MIN ( 'Payments'[Date] ) -- dates of the payments made for the investments 
VAR LastDateWithCashflow =
 
MAX ( 'Payments'[Date] ),
REMOVEFILTERS ()

VAR Result =
IF (
FirstVisibleDate <= LastDateWithCashflow,
CALCULATE (
SUM('Payments'[Payments]),
'Date'[Date] <= LastVisibleDate
)
)
 
RETURN
Result

The Payments table looks something like this:
DatePaymentsInvestment in Company
15-Apr-05                           10 A
30-Jun-08                           40 B
31-Dec-09                           20 A
31-Dec-09                           80 B

The "ownership Boolean" table (a "1" means that we own it at that point in time and a "0" means that we don't):
 
 


So yes, how do I modify the running total measure ("Cash Cost") to only show a number when "Own?" equals 1? Thank you
  • Anonymous's avatar
    Anonymous
    5 years ago
    // This will work OK only IFF for each
    // company there exists AT MOST 1 day
    // in the Payments table.
    
    [Cumul Payment] =
    var __lastVisiblDate =
        MAX( Date[Date] )
    var __onlyOneCompanyVisible =
        // Company should be a dimension
        // that joins to Payments, so please
        // do yourself a favour and make your
        // model into a proper star schema.
        // This code assumes that Company
        // exists only in your fact table
        // Payments but this is NOT how it
        // should be. If you have Company
        // as a dimension, you'll change the
        // code below to
        // HASONEVALUE( Company[CompanyID] )
        // instead.
        HASONEVALUE( Payments[Company] )
    var __result =
        if( __onlyOneCompanyVisible,
            var __weOwnTheInvestment =
                CALCULATE(
                    SELECTEDVALUE( Payments[Own?], 0 ) = 1,
                    // This is where you have to make
                    // sure that the assumption from
                    // above is observed. Otherwise,
                    // the value of CALCULATE will always
                    // return False.
                    Date[Date] = __lastVisibleDate
                )
            RETURN
            if( __weOwnTheInvestment,
                CALCULATE(
                    SUM( Payments[Payment] ),
                    // Date must be a date table
                    // in the model marked as such
                    // for this to work OK.
                    Date[Date] <= __lastVisibleDate
                    
                    // If you want to only sum up
                    // the payments where [Own?] is 1
                    // then you have to add as a filter
                    // this condition to this CALCULATE:
                    // Payments[Own?] = 1.
                    // But if you do this, you'll only
                    // get a running total for the
                    // payments that you own, not the
                    // true running total. But maybe this is
                    // what you want... Nevertheless, the code
                    // only displays the total if the
                    // max day for the visible company
                    // has the flag set to 1 regardless
                    // of the method of summation.
                )
            )
        )
    return
        __result

11 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    // This will work OK only IFF for each
    // company there exists AT MOST 1 day
    // in the Payments table.
    
    [Cumul Payment] =
    var __lastVisiblDate =
        MAX( Date[Date] )
    var __onlyOneCompanyVisible =
        // Company should be a dimension
        // that joins to Payments, so please
        // do yourself a favour and make your
        // model into a proper star schema.
        // This code assumes that Company
        // exists only in your fact table
        // Payments but this is NOT how it
        // should be. If you have Company
        // as a dimension, you'll change the
        // code below to
        // HASONEVALUE( Company[CompanyID] )
        // instead.
        HASONEVALUE( Payments[Company] )
    var __result =
        if( __onlyOneCompanyVisible,
            var __weOwnTheInvestment =
                CALCULATE(
                    SELECTEDVALUE( Payments[Own?], 0 ) = 1,
                    // This is where you have to make
                    // sure that the assumption from
                    // above is observed. Otherwise,
                    // the value of CALCULATE will always
                    // return False.
                    Date[Date] = __lastVisibleDate
                )
            RETURN
            if( __weOwnTheInvestment,
                CALCULATE(
                    SUM( Payments[Payment] ),
                    // Date must be a date table
                    // in the model marked as such
                    // for this to work OK.
                    Date[Date] <= __lastVisibleDate
                    
                    // If you want to only sum up
                    // the payments where [Own?] is 1
                    // then you have to add as a filter
                    // this condition to this CALCULATE:
                    // Payments[Own?] = 1.
                    // But if you do this, you'll only
                    // get a running total for the
                    // payments that you own, not the
                    // true running total. But maybe this is
                    // what you want... Nevertheless, the code
                    // only displays the total if the
                    // max day for the visible company
                    // has the flag set to 1 regardless
                    // of the method of summation.
                )
            )
        )
    return
        __result
    • Anonymous's avatar
      Anonymous
      Not applicable

      Anonymous 

       , almost! It's just that the totals are blank.

      Thank you for your advice. I have improved my model into a proper star schema. So I've done 3 things:

      1) I've added a Dimension table of company names

      2) Now I'm treating the Ownership table as a fact table. Previously I had naively treated it as a Dimension table but upon reading more Power BI articles, I've realised that it is actually a "slowly changing dimension" and that a Dimension table must have a Dimension Key which my Ownership table of time-varying ownership of companies doesn't have.

      3) Therefore I've edited your measure from using HASONEVALUE( Payments[Company] ) to something like HASONEVALUE( Company[Company ID] )

      I've turned my dashboard report into a stripped-down dummy one  with fake names and figures here https://www.dropbox.com/s/njy7m84htsul4r7/Dannial%20for%20Forum.pbix?dl=0

      A good sample is company Z-Corp which was no longer owned starting 31-Mar-2018. So correct, it's running total (cumulative payments) is blank on that date onwards (note: all my dates are end of quarter). But it's just that there are no totals nor subtotals (by company nor by year):

      So how can one make the totals appear?

      • Anonymous's avatar
        Anonymous
        Not applicable

        Anonymous , Greg_Deckler 

        After reading a few articles, I think I know why the totals are blank - because their Own > 1.

        So I've tried changing the codes from:

        __weOwnTheInvestment =

                    CALCULATE(

                        SELECTEDVALUE( 'Ownership'[Own?], 0 ) = 1,

                        'Date'[Date] = __lastVisibleDate

        to:

        __weOwnTheInvestment =

                    CALCULATE(

                        SELECTEDVALUE( 'Ownership'[Own?], 0 ) > 0,

                        'Date'[Date] = __lastVisibleDate

        But the totals (at each date) are still blanks.
        The articles I've read also had mentioned that typically for the totals to equal the visual totals, one should use the function SUMX.

        However, I think that SUMX's arguments must be from the same table whereas mine is more like:

        Cumul Payment if Owned = SUMX( ? , [Cumul Payment]* 'Ownership'[Own?])

        Basically a SUMPRODUCT

        So how can one get the totals to be equal to the visual total? Would I need to, and would it even be possible, to create a calculated table that contains: 'Date'[Date]; 'Company'[Company]; Cumul Payment; and 'Ownership'[Own?] so that I can do a SUMX? 

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion

    Anonymous Maybe:

    Cash Cost  =
    VAR LastVisibleDate =
    MAX ( 'Date'[Date] ) -- my date table and date column
    VAR FirstVisibleDate =
    MIN ( 'Payments'[Date] ) -- dates of the payments made for the investments 
    VAR LastDateWithCashflow =
     
    MAX ( 'Payments'[Date] ),
    REMOVEFILTERS ()
    
    VAR Result =
    IF (
    FirstVisibleDate <= LastDateWithCashflow,
    CALCULATE (
    SUM('Payments'[Payments]),
    'Date'[Date] <= LastVisibleDate,
    'Ownership'[Own?]=1
    )
    )
     
    RETURN
    Result
    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi Greg_Deckler . Thanks but I've actually tried that. Oh, by the way, sorry, I forgot to type CALCULATE for the MAX and REMOVEFILTERS bit. Anyway therefore I guess the source of my error is the relationships in my data model then:


      Initially I was hoping to (naively)  link 'Date'[Date] with 'Ownership'[Date] as well only to discover that that's not allowed because there would be an indirect relationship or introduce ambiguity.

      • Anonymous's avatar
        Anonymous
        Not applicable
        Please show us a relevant example of what you really want. Thanks.