topic i have measure like this im unable to get cumilative total can you people suggest some idea in DAX Commands and Tips

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

tkavitha911 — Thu, 19 Sep 2024 07:27:04 GMT

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

)

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

123abc — Thu, 19 Sep 2024 07:36:26 GMT

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

Use the EARLIER function to ensure the calculation correctly handles row context for cumulative totals.
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!

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

tkavitha911 — Thu, 19 Sep 2024 07:40:26 GMT

im unable to get grand total can you help me

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

tkavitha911 — Thu, 19 Sep 2024 08:09:49 GMT

this is the out pou im getting

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

123abc — Thu, 19 Sep 2024 14:42:49 GMT

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:

Cumulative Total: For each row, we calculate the cumulative total as before.
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.
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.

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

123abc — Tue, 24 Sep 2024 12:17:12 GMT

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

Check Sorting: Ensure that your data is sorted correctly. Cumulative totals depend on the data being sorted by the appropriate index or date column.
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.