i have measure like this im unable to get cumilative total can you people suggest some idea

[tkavitha911]

CumulativeInvVol =

VAR CurrentRowIndex = MAX(IHR[Index])

VAR CurrentCategory = SELECTEDVALUE(IHR[Time Until Expiry (M)])

VAR IsFirstInCategory =

    CurrentRowIndex =

    CALCULATE(

        MIN(IHR[Index]),

        FILTER(

            ALL(IHR),

            IHR[Time Until Expiry (M)] = CurrentCategory

        )

    )

VAR PreviousCumulative =

    CALCULATE(

        [Inventory Volume],

        FILTER(

            ALL(IHR),

            IHR[Index] < CurrentRowIndex

            && IHR[Time Until Expiry (M)] = CurrentCategory

        )

    )

RETURN

    IF(

        IsFirstInCategory,

        [Inventory Volume],

        [Inventory Volume] + PreviousCumulative

    )

[tkavitha911]

this is the out pou im getting 

[123abc · Accepted solution]

To ensure that the cumulative total is calculated correctly, we can take the following steps:

1. Check Sorting: Ensure that your data is sorted correctly. Cumulative totals depend on the data being sorted by the appropriate index or date column.

2. Modify the Formula: Let's refine the formula further to handle the cumulative total explicitly.

Here’s a modified version of the DAX measure:

CumulativeInvVol2 =
VAR CurrentRowIndex = MAX(IHR[Index])
VAR CurrentCategory = SELECTEDVALUE(IHR[Time Until Expiry (M)])
VAR CumulativeSum =
CALCULATE(
SUM(IHR[Inventory Volume]),
FILTER(
ALL(IHR),
IHR[Index] <= CurrentRowIndex &&
IHR[Time Until Expiry (M)] = CurrentCategory
)
)
RETURN
IF(ISBLANK(CurrentCategory), BLANK(), CumulativeSum)

Key Details:

• SUM(IHR[Inventory Volume]): This ensures you are summing the Inventory Volume as you move through the rows.
• IHR[Index] <= CurrentRowIndex: The cumulative sum includes all rows up to the current row.
• FILTER(ALL(IHR)): The ALL(IHR) removes any existing filters on the table to ensure that the cumulative calculation can consider all rows.

[tkavitha911]

im unable to get grand total can you help me 

[123abc]

Here's the updated version of your CumulativeInvVol measure to correctly display the grand total:

CumulativeInvVol =
VAR CurrentRowIndex = MAX(IHR[Index])
VAR CurrentCategory = SELECTEDVALUE(IHR[Time Until Expiry (M)])

VAR CumulativeTotal =
CALCULATE(
SUM(IHR[Inventory Volume]),
FILTER(
ALL(IHR),
IHR[Index] <= CurrentRowIndex && IHR[Time Until Expiry (M)] = CurrentCategory
)
)

VAR TotalVolumeForCategory =
CALCULATE(
SUM(IHR[Inventory Volume]),
ALL(IHR),
IHR[Time Until Expiry (M)] = CurrentCategory
)

RETURN
IF(
ISINSCOPE(IHR[Index]),
CumulativeTotal,
TotalVolumeForCategory
)

Adjustments:

1. Cumulative Total: For each row, we calculate the cumulative total as before.
2. Total Volume for Category: We calculate the total volume for the entire category without row-level filters using ALL(IHR) and return this value for the grand total.
3. ISINSCOPE: The ISINSCOPE function checks whether you're currently at a row-level context (i.e., not at the grand total level). If you're at a row level, the cumulative total is returned; otherwise, the total volume for the category is shown in the grand total.

[123abc]

Here’s an adjusted approach for calculating the cumulative inventory volume:

1. Use the EARLIER function to ensure the calculation correctly handles row context for cumulative totals.
2. Use the CALCULATE function with FILTER to accumulate inventory volumes for all previous rows.

Here is a modified version of your measure:

CumulativeInvVol =
VAR CurrentRowIndex = MAX(IHR[Index])
VAR CurrentCategory = SELECTEDVALUE(IHR[Time Until Expiry (M)])

VAR CumulativeTotal =
CALCULATE(
SUM(IHR[Inventory Volume]),
FILTER(
ALL(IHR),
IHR[Index] <= CurrentRowIndex && IHR[Time Until Expiry (M)] = CurrentCategory
)
)

RETURN
CumulativeTotal

• The FILTER function is applied to all rows (ALL(IHR)), and it checks two conditions: the current index and the matching category.
• The cumulative sum (SUM(IHR[Inventory Volume])) is calculated for rows that have an index less than or equal to the current row and belong to the same category.

This version simplifies the logic and ensures the cumulative total is calculated properly. Let me know if this works for your scenario, or if you'd like further clarification!