Forum Discussion
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):
VAR 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
- Anonymous5 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
- AnonymousNot 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- AnonymousNot 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?- AnonymousNot 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_DecklerCommunity 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- AnonymousNot 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.- AnonymousNot applicablePlease show us a relevant example of what you really want. Thanks.