Cumulative total and Running total with many to many

[sreddy47652]

Hi Team, i have 2 tables and it's having many to many relationship between those tables so now i am trying to create measures between Running total and cumulative count but it was giving same values as measure and other way it's throwing visual exceed memory issue even i have 15K records, i tries to create measures like Quick measure and Dax but still it's not working so can you please guide me how to get Running total and cumulative count with many to many relationship. I don't have any date table in between my model as well.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but if your visualization is working correctly on the power bi report page, try using Visual Calculation to get the result of running sum or cumulate count.

Using visual calculations in Power BI Desktop - Power BI | Microsoft Learn

[rajendraongole1]

Hi @sreddy47652 - You can create a bridge table that contains unique values from both tables for the key columns. This will let you manage the relationship effectively 

I hope you have seperate date table in your model that can be calculating cumulative counts or running totals over time, adding a date table can simplify the DAX logic.

Below is the measure and replace with your tables.

RunningTotal =
CALCULATE(
SUM('YourTable'[ValueColumn]),
FILTER(
ALL('YourTable'[OrderColumn]), -- Replace OrderColumn with Date or Sequential Column
'YourTable'[OrderColumn] <= MAX('YourTable'[OrderColumn])
)
)

cumulative count measure:

CumulativeCount =
CALCULATE(
COUNT('YourTable'[ID]),
FILTER(
ALL('YourTable'[OrderColumn]),
'YourTable'[OrderColumn] <= MAX('YourTable'[OrderColumn])
)
)

hope this works