Forum Discussion
Running Total with If
- 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
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