Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi,
My goal is: creating a pivot table which presents cumulative total amount of all transactions labeled as "Principal Repayment", up to a certain point in time. This point in time is specified by the users in form of a slicer or column of the pivot table. The transctaions occur in many currencies, so the pivot table must distinguish the cumulative amount by currencies
My data:
My 1st measure:
Cumulative Principal Repayment :=
CALCULATE (
SUM ( 'Transaction'[Amount] ),
FILTER (
ALLEXCEPT ( 'Transaction', 'Transaction'[Currencies] ),
AND (
'Transaction'[Transaction Date] <= MAX ( 'Transaction'[Transaction Date] ),
'Transaction'[Label] = "Principal Repayment"
)
)
)
This measure uses the foreign key ('Transaction'[Currencies]), and when I create a pivot table, using 'Transaction'[Currencies] as column, the results is as expected
The problem: if I rewrite the 1st measure by replacing 'Transaction'[Currencies] with 'Currencies'[Currencies], the measure returns an error.
My modified measure:
Cumulative Principal Repayment :=
CALCULATE (
SUM ( 'Transaction'[Amount] ),
FILTER (
ALLEXCEPT ( 'Currencies', Currencies[Currencies] ),
AND (
'Transaction'[Transaction Date] <= MAX ( 'Transaction'[Transaction Date] ),
'Transaction'[Label] = "Principal Repayment"
)
)
)
Since there are other measures must be filtered simultaneously with 'Cumulative Principal Repayment" by Currencies, I cannot use the first measure.
Thank you for your help,
When you are testing both your measures, is the column that is providing row context also change to keep up with the measure?
I ask because in your first measure, your ALLEXCEPT is on Transaction Table, Currencies Field. In the second measure it is now on the Currencies Table, Currencies Field. If you have simply taken your new measure and placed it into the same context as the previous measure the ALLEXCEPT.
I also notice that one of your filters is based on a date in the Transaction table, however in the 2nd measure you are no longer doing an ALL on that table.
I'd also recommend changing your first measure to avoid using the FILTER function and use this type of structure:
Cumulative Principal Repayment = VAR filterDate = MAX('Transaction'[Transaction Date]) RETURN CALCULATE( SUM('Transaction'[Amount]), ALLEXCEPT('Transaction', 'Transaction'[Currencies]), 'Transaction'[Transaction Date] <= filterDate, 'Transaction'[Label] = "Principal Repayment" )
Second Measure:
Cumulative Principal Repayment = VAR filterDate = MAX('Transaction'[Transaction Date]) RETURN CALCULATE( SUM('Transaction'[Amount]), ALLEXCEPT('Currencies', Currencies[Currencies]]), 'Transaction'[Transaction Date] <= filterDate, 'Transaction'[Label] = "Principal Repayment" )
User | Count |
---|---|
121 | |
69 | |
67 | |
57 | |
50 |
User | Count |
---|---|
176 | |
83 | |
69 | |
65 | |
54 |