Forum Discussion
Running Total over a CROSSFILTER
I have a situation and I am not sure how to get out of it. I have 3 tables:
fChargebacks:
Date | Tran Amount$
dIncomingDate
DateKey | YearWeek
fCashCharges
YearWeek| Amount$
Relationships: (1)IncomingDate -> (*) fChargebacks on DateKey
(1)IncomingDate -> (*) fChargebacks on DateKey
(1)fCashCharges -> (*) IncomingDate on YearWeek
The last table is basically only showing data grouped by the year and week, while the first table has distinct dates. The YearWeek fields are numbers (like 201401, 201402, ... 201452), so they can be used in a comparison or with MIN/MAX:
I want to show a report that has:
YearWeek | Total Chargeback$ | Total CashCharges$ | Cumulative Chargeback$ | Cumulative CashCharges$
The Cumulative CCCharge is where I get stuck.
This is how the measures are defined:
Total Chargebacks$:=SUMX(FILTER(fChargebacks, fChargebacks[Tran Amount]>0), fChargebacks[Tran Amount])
Total CashCharges$:=CALCULATE(SUM(fCashCharges[Amount]), CROSSFILTER(dIncomingDate[YearWeek],fCashCharges[YearWeek],Both))
Cumulative Chargebacks$:=CALCULATE (
SUM ( fChargebacks[Tran Amount]),
FILTER ( ALL(dIncomingDate[YearWeekNumber]), dIncomingDate[YearWeekNumber]<=MAX(dIncomingDate[YearWeekNumber])))
But I am stuck at Cumulative CashCharges. Because I have to manually create the relationship to filter the YearWeek key, I'm not sure how to accumulate the amounts. I tried some ways but none of them worked. I'm thinking that I have to CROSSFILTER and then filter again, but I'm not sure. I think also the problem might arise because the relationship between the fCashCharges and IncomingDate is strange and awkward. I don't know if that's the way to go. But basically IncomingDate as a dimension table is sitting between these two fact tables, but one has data at date level, while the other is already grouped at year-week level.
If anybody can give me some idea on where to go from here, I'd appreciate it.
Thank you,
Iulian
Iulian
In this scenario, you must set the relationship between the dIncomingDate and fCashCharges into "Both" cross filter direction.
Then you can just create a cumulative CashCharge measure like:
Cumulative CashCharge = CALCULATE(SUM(CashCharge[Amount]),FILTER(ALL(CashCharge),CashCharge[YearMonth]<=MAX(CashCharge[YearMonth])))
Make sure you drag the YearMonth column from CashCharge Table into your table visual. See my sample below:
I also attached my .pbix file.
2 Replies
- v-sihou-msft
Microsoft Employee
In this scenario, you must set the relationship between the dIncomingDate and fCashCharges into "Both" cross filter direction.
Then you can just create a cumulative CashCharge measure like:
Cumulative CashCharge = CALCULATE(SUM(CashCharge[Amount]),FILTER(ALL(CashCharge),CashCharge[YearMonth]<=MAX(CashCharge[YearMonth])))
Make sure you drag the YearMonth column from CashCharge Table into your table visual. See my sample below:
I also attached my .pbix file.
- iulienelNew Member
Thank you very much!!