cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Cumulative Total with two columns

Hello Everyone,

I'm dealing with following scenario.

We have table with following structure:

What I would like to do is:

Calculate accumulative total for POS_AMT measure based on WEEK_END_DATE.

I did that using following formula:

```POS_AMT_CUMULATIVE =
CALCULATE(
SUM(ARD_POS_FRCST_WKLY[POS_AMT]);
FILTER(
ALLEXCEPT(ARD_POS_FRCST_WKLY; ARD_PRODUCT_DIM[PRODUCT_SECTOR_NAME];ARD_PRODUCT_DIM[PRODUCT_CATEGORY_NAME]);
ARD_POS_FRCST_WKLY[WEEK_END_DATE] <= MAX(ARD_POS_FRCST_WKLY[WEEK_END_DATE])
)
)```

However, at certain point, POS_AMT values for few weeks are Blanks, with just FORECAST_AMT column being filed.

What I would want to achieve is -> For WEEK_END_DATE values that have FORECAST_AMT, but doesn't have POS_AMT use FORECAST_AMT insead of POS_AMT in cumulative calculation.

The other way would be to calculate cumulative POS_AMT for only those weeks that have such values and later on calculate another measure with FORECAST_AMT, but only for those weeks that doesn't have POS_AMT.

Hope it's clear enough... 🙂

1 ACCEPTED SOLUTION
Microsoft Employee

@Glaeran

Hi,

This formula can mark the first week that starts using FORECAST_AMT. Maybe you need to make some changes. It's used as calculated column. Hope this would help.

```Column =
IF (
ARD_POS_FRCST_WKLY[WEEK_END_DATE]
= CALCULATE (
MIN ( ARD_POS_FRCST_WKLY[WEEK_END_DATE] ),
FILTER ( ARD_POS_FRCST_WKLY, ARD_POS_FRCST_WKLY[POS_AMT] = 0 )
),
1,
0
)```

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Microsoft Employee

@Glaeran

Hi,

You just need to change SUM into SUMX. Try this one below.

POS_AMT_CUMULATIVE =

CALCULATE (

SUMX (

ARD_POS_FRCST_WKLY,

IF (

ARD_POS_FRCST_WKLY[POS_AMT] = 0,

ARD_POS_FRCST_WKLY[FORECAST_AMT],

ARD_POS_FRCST_WKLY[POS_AMT]

)

),

FILTER (

ALLEXCEPT (

ARD_POS_FRCST_WKLY,

ARD_PRODUCT_DIM[PRODUCT_SECTOR_NAME],

ARD_PRODUCT_DIM[PRODUCT_CATEGORY_NAME]

),

ARD_POS_FRCST_WKLY[WEEK_END_DATE] <= MAX ( ARD_POS_FRCST_WKLY[WEEK_END_DATE] )

)

)

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

Thank you!

That actually is working almost as expected.

"Almost", because our "ideal" scenario, would be to have those values that are using [FORECAST_AMT} column to be colored separately. Is it doable though?

Microsoft Employee

@Glaeran

Hi,

We can’t color them separately due to we accumulate the total. If the first  month uses FROCAST_AMT, we can say the last month uses it too. If we want to find out the current month using it or not, we can add a calculated column, and then add it to legend. This doesn’t work with area chart. You can have a look at the image below. The black column means this column uses FROCAST_AMT. Is this what you want?

UsingFrocast =

CALCULATE (

IF ( MIN ( ARD_POS_FRCST_WKLY[POS_AMT] ) = 0, 1, 0 ),

FILTER (

ARD_POS_FRCST_WKLY,

MONTH ( ARD_POS_FRCST_WKLY[WEEK_END_DATE] )

= MONTH ( EARLIER ( ARD_POS_FRCST_WKLY[WEEK_END_DATE] ) )

)

)

Best regards

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

@v-jiascu-msftUnfortunately not :(.

Our goal is to show both accumulative POS_AMT and FORECAST_AMT on 1 chart (preferably Area Chart or Line Chart), with the same idea as before so:

If WEEK_END_DATE has POS_AMT show POS_AMT otherwise show FORECAST_AMT, or by any chance "mark" the week in which we're starting to use FORECAST_AMT.

Microsoft Employee

@Glaeran

Hi,

This formula can mark the first week that starts using FORECAST_AMT. Maybe you need to make some changes. It's used as calculated column. Hope this would help.

```Column =
IF (
ARD_POS_FRCST_WKLY[WEEK_END_DATE]
= CALCULATE (
MIN ( ARD_POS_FRCST_WKLY[WEEK_END_DATE] ),
FILTER ( ARD_POS_FRCST_WKLY, ARD_POS_FRCST_WKLY[POS_AMT] = 0 )
),
1,
0
)```

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors