topic Re: Running Total with If in DAX Commands and Tips

Running Total with If

Anonymous — Wed, 23 Sep 2020 04:20:31 GMT

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:

Date	Payments	Investment 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

Re: Running Total with If

Greg_Deckler — Wed, 23 Sep 2020 04:49:08 GMT

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

Re: Running Total with If

Anonymous — Wed, 23 Sep 2020 06:42:19 GMT

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.

Re: Running Total with If

Anonymous — Wed, 23 Sep 2020 07:28:16 GMT

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

Re: Running Total with If

Anonymous — Wed, 23 Sep 2020 09:06:34 GMT

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:

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:

Re: Running Total with If

Anonymous — Wed, 23 Sep 2020 10:15:56 GMT

// 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

Re: Running Total with If

Anonymous — Fri, 25 Sep 2020 15:12:32 GMT

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?

Re: Running Total with If

Anonymous — Mon, 28 Sep 2020 13:44:56 GMT

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?

Re: Running Total with If

Greg_Deckler — Mon, 28 Sep 2020 16:12:41 GMT

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

Re: Running Total with If

Anonymous — Mon, 28 Sep 2020 16:41:20 GMT

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.

Re: Running Total with If

Anonymous — Tue, 29 Sep 2020 00:39:44 GMT

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:

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

Re: Running Total with If

Anonymous — Tue, 29 Sep 2020 09:01:25 GMT

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.