Forum Discussion
Helper ICumulative total on two tables
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.
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
3 Replies
camargos88Community Champion
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
srinivas258Hi Anonymous 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.