cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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):
 
 

Ownership Boolean.PNG
So yes, how do I modify the running total measure ("Cash Cost") to only show a number when "Own?" equals 1? Thank you
1 ACCEPTED SOLUTION
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

View solution in original post

11 REPLIES 11
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
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):
No Total.PNG
So how can one make the totals appear?

Anonymous
Not applicable

Question 1: Why would you store the bool flag [Own?] as something else than a True/False flag?

Question 2: From what you've said above I understand that when you are saying "cumulative" it means you only want to aggregate from beginning to the current date but only over those dates for which the flag is True? Am I correct?

Question 3: I don't quite get how the total row should work. If the company in question (say, there's only one visible) has the last day visible in the context (which means on the total row) with the flag set to False, you can't display the total because you'd violate the rules of the measure. If you do want to display any kind of total, then you have to modify the rules of your measure. It is to say that the rules of calculation must be consistent and with the current setup and your 'desire' they are not.
Anonymous
Not applicable

@Anonymous 
Question 1: Why would you store the bool flag [Own?] as something else than a True/False flag?

Answer: If I store [Own?] as 1s and 0s, I can multiply [Own?] with the unconditional running total (which I can already do). Then perhaps I can sum them for a particular date. For example, for one date which is 30th June 2020:

SUMX Cumul Payment.PNG

Question 2: From what you've said above I understand that when you are saying "cumulative" it means you only want to aggregate from beginning to the current date but only over those dates for which the flag is True? Am I correct?

Answer: Yes, correct.
 
Question 3: I don't quite get how the total row should work. If the company in question (say, there's only one visible) has the last day visible in the context (which means on the total row) with the flag set to False, you can't display the total because you'd violate the rules of the measure. If you do want to display any kind of total, then you have to modify the rules of your measure. It is to say that the rules of calculation must be consistent and with the current setup and your 'desire' they are not.

My reply: OK, I see. Thanks

Anonymous
Not applicable

As for the True/False flag: YES, YOU CAN MULTIPLY BOOLS WITH NUMBERS and they'll be treated then as 1 and 0, respectively. In DAX, there's no way to tell (in a visual) whether you're on the total row or not. If, say, you choose months instead of days to be displayed on rows and the total will therefore have all the months in a year, you will display the cumulatives for only those months where the last day has the [Own?] flag set to True. Otherwise, you'd display BLANK. Same goes for the total - whether you display a figure or not depends on what there is in the last day. This would be very messy. On the other hand, if you say "If there is more than 1 day visible in the context, display the cumulative until the last day in the period that has the flag set to True", you will almost get what you want apart from the fact that there may be a BLANK total whereas most of the months will have a figure.... The way you want to calculate your measure, or rather display it, is hard to make consistent. I'd think about a different way: calculate the cumulative for ANY day but use the flag to only include the "True" days. This way, you'd easily implement a total that would be consistent with your detail rows.
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? 

@Anonymous - This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition
Greg_Deckler
Super User
Super User

@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

@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition
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:

Running Total Data Model.PNG
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
Not applicable

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

Hi @Anonymous (cool nickname btw, very apt). Sure, I'd be happy to. You've probably noticed that I stopped owning the investment A starting 31 Jan 2010 and stopped owning B starting 14 Feb 2010. Therefore in a Matrix, I would like to have a (conditional) running total like this:

Desired Output Conditional Running Total.PNG
However despite using 

CALCULATE (

SUM('Payments'[Payments]),

'Date'[Date] <= LastVisibleDate,

'Ownership'[Own?]=1)

)

, the condition " 'Ownership'[Own?]=1 " doesn't have an effect and I get a Matrix like this instead:
Wrong Output Conditional Running Total.PNG

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.