Cumulative total on two tables

[srinivas258]

Hello all,

Below are the two tables I have in my model (Fact and Dim). The join between the two tables is based on ID.

Fact Table
ID	Total	Stop
100	3	1
200	5	2
300	4	3
400	1	4

Dim
ID	Name
100	AA
200	BB
300	CC
400	DD

Output :

Output
Stop	Name	Total	Running Total
1	AA	3	3
2	BB	5	8
3	CC	4	12
4	DD	1	13

I got the running total working using the below formula but it works as long as I have columns from Fact table ONLY. As soon as I place column from Dim table (i.e Name) the running total stops working and it displays the actual totals instead.

RT = CALCULATE(SUM(Table[Fact Table]),filter(allselected(Fact Table),Fact Table[Stop] <=max(Fact Table[Stop])))

I think I need to extend my DAX further and add Dim table to above formula but I am struck how this can be done. Can anyone please help on this. Thank you.

[v-yiruan-msft · Accepted solution]

Hi @camargos88 ,

Based on the data and formula you currently provide, it can return the correct value. Could you please provide some screen shots about the below scenario? We need this information to see if we missed anything...

---

As soon as I place column from Dim table (i.e Name) the running total stops working and it displays the actual totals instead.

---

Best Regards

Rena

[srinivas258]

Hi @v-yiruan-msft  and @camargos88 

Thanks for you inputs. Actually upon investigation found that the join between fact and dim is many to many which is causing the issue. There are some duplicates in dim table which we removed from backend table and the formula that I posted worked without any issues.  Thanks again it was backend table issue at the end. 

[camargos88]

Hi @srinivas258 ,

I tried your measure here, it worked. 

How are you relating the tables ?

Ricardo