Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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):
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 |
Solved! Go to Solution.
// 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
// 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
, 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
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
@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
@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
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.
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:
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |